SQLite里的SQL
SQL As Understood By SQLite

[Top]

VACUUM

vacuum-stmt: 隐藏

VACUUM命令会重建整个数据库。当遇到如下情况时应用可以考虑使用该命令：
The VACUUM command rebuilds the entire database. There are several reasons an application might do this:

• 如果SQLite没有运行在"auto_vacuum=FULL"模式下，那么当从数据库文件中删除大量数据后，数据库会遗留下来许多空空间，而不会"释放"数据库页。这就是说，数据库文件可能比实际所需的空间要大。运行VACUUM可以重建这个数据库，回收空间，减少数据库文件的大小。
  Unless SQLite is running in "auto_vacuum=FULL" mode, when a large amount of data is deleted from the database file it leaves behind empty space, or "free" database pages. This means the database file might be larger than strictly necessary. Running VACUUM to rebuild the database reclaims this space and reduces the size of the database file.

• 频繁的插入、更新和删除操作会导致数据库文件碎片化——一个表或索引中的数据会被分散到整整数据库文件中。运行VACUUM可以确保每个表或索引都尽可能的在数据库文件中连续存储。在一些情况下，VACUUM还可以减少数据库中部分填满的页数量，从而进一步减少数据库文件尺寸。
  Frequent inserts, updates, and deletes can cause the database file to become fragmented - where data for a single table or index is scattered around the database file. Running VACUUM ensures that each table and index is largely stored contiguously within the database file. In some cases, VACUUM may also reduce the number of partially filled pages in the database, reducing the size of the database file further.

• 通常在数据库文件实际创建之前就必须配置好数据库page_size和是否支持auto_vacuum。不过，只要不是write-ahead log模式，已有数据库的page_size 和 auto_vacuum参数都可以通过使用pragma page_size 和 pragma auto_vacuum指令来修改，这样会立即VACUUM数据库。如果是write-ahead log模式，那么只能修改auto_vacuum属性。
  Normally, the database page_size and whether or not the database supports auto_vacuum must be configured before the database file is actually created. However, when not in write-ahead log mode, the page_size and/or auto_vacuum properties of an existing database may be changed by using the page_size and/or pragma auto_vacuum pragmas and then immediately VACUUMing the database. When in write-ahead log mode, only the auto_vacuum support property can be changed using VACUUM.

VACUUM只能在主数据库上使用，不能在附加库上使用VACUUM。
VACUUM only works on the main database. It is not possible to VACUUM an attached database file.

VACUUM命令首先将数据库的内容复制到一个临时数据库文件中，然后再使用临时文件覆盖原有数据库。当覆盖原始库的时候，对回滚日志或write-ahead logWAL文件的使用就像是在其它数据库事务中一样。也就是说，数据库在执行VACUUM时，大约需要原始数据库文件两倍大小的空闲磁盘空间。
The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file. When overwriting the original, a rollback journal or write-ahead log WAL file is used just as it would be for any other database transaction. This means that when VACUUMing a database, as much as twice the size of the original database file is required in free disk space.

VACUUM命令会修改没有明确指定INTEGER PRIMARY KEY的表的记录的ROWIDs。
The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.

如果有开始的事务或者有一条以上SQL语句正在执行，那么VACUUM会失败。
A VACUUM will fail if there is an open transaction, or if there are one or more active SQL statements when it is run.

从SQLite3.1版开始，增加了一个使用VACUUM命令在数据被删除后回收空间的方法，就是使用auto_vacuum指令开启auto-vacuum模式。当开启了auto_vacuum模式，无需使用VACUUM命令重建整个数据库，就可以在删除数据后空闲的页会被回收，并压缩文件大小。不过使用auto_vacuum会增加数据库文件的碎片化。并且auto_vacuum无法像VACUUM一样压缩部分填满的页。
As of SQLite version 3.1, an alternative to using the VACUUM command to reclaim space after data has been deleted is auto-vacuum mode, enabled using the auto_vacuum pragma. When auto_vacuum is enabled for a database free pages may be reclaimed after deleting data, causing the file to shrink, without rebuilding the entire database using VACUUM. However, using auto_vacuum can lead to extra database file fragmentation. And auto_vacuum does not compact partially filled pages of the database as VACUUM does.