在“DELETE FROM table"之后更改sqlite文件大小 [英] change sqlite file size after "DELETE FROM table"

问题描述

我正在处理 sqlite3 文件.

I am working with sqlite3 file.

首先，我进入了比较大的数据库，文件大小约为 100 mb.

First, I entered relatively big database, file size was about 100 mb.

比我做的多

$db->exec("DELETE FROM table");

并只输入了该数据库的一小部分.但文件大小仍然是 100 MB.

and entered just a small part of that database. But file size remained 100 mb.

删除内容时如何更改sqlite文件大小?

What should you do to change sqlite file size when deleting it's content?

推荐答案

您要查找的命令是 vacuum.还有一个 pragma 可以打开自动吸尘.

The command you are looking for is vacuum. There is also a pragma to turn auto-vacuuming 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.

这篇关于在“DELETE FROM table"之后更改sqlite文件大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！