为什么清除 SQLite 数据库不会减小其大小? [英] Why does clearing an SQLite database not reduce its size?

问题描述

我有一个 SQLite 数据库.

I have an SQLite database.

我创建了表格并填充了大量数据.

I created the tables and filled them with a considerable amount of data.

然后我通过删除和重新创建表来清除数据库.我使用 SQLite Administrator 查看所有数据并确认表为空.

Then I cleared the database by deleting and recreating the tables. I confirmed that all the data had been removed and the tables were empty by looking at them using SQLite Administrator.

问题是数据库文件(*.db3)被清除后大小保持不变.

The problem is that the size of the database file (*.db3) remained the same after it had been cleared.

这当然是不可取的，因为我想在清除数据后重新获得数据占用的空间.

This is of course not desirable as I would like to regain the space that was taken up by the data once I clear it.

有没有人做过类似的观察和/或知道发生了什么?

Did anyone make a similar observation and/or know what is going on?

可以做些什么?

推荐答案

来自这里:

当一个对象(表、索引、触发器或视图)从数据库中删除时，它会留下空白空间.下次将新信息添加到数据库时，将重新使用此空白空间.但与此同时，数据库文件可能比严格需要的要大.此外，频繁的插入、更新和删除会导致数据库中的信息变得碎片化——分散在整个数据库文件中，而不是聚集在一个地方.

When an object (table, index, trigger, or view) is dropped from the database, it leaves behind empty space. This empty space will be reused the next time new information is added to the database. But in the meantime, the database file might be larger than strictly necessary. Also, frequent inserts, updates, and deletes can cause the information in the database to become fragmented - scrattered out all across the database file rather than clustered together in one place.

VACUUM 命令通过将主数据库的内容复制到临时数据库文件并从副本重新加载原始数据库文件来清理主数据库.这会消除空闲页面，将表数据对齐为连续的，否则会清理数据库文件结构.

The VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure.

这篇关于为什么清除 SQLite 数据库不会减小其大小?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！