database 返回 SQLite 数据库中表大小的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27572387/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query that returns the size of a table in a SQLite database
提问by Tony Vitabile
I have a SQLite database that contains a number of tables. We are writing a maintenance tool that will remove "stale" data from a table until that table's size is a certain percentage of the total database file or smaller.
我有一个包含许多表的 SQLite 数据库。我们正在编写一个维护工具，它将从表中删除“陈旧”数据，直到该表的大小等于或小于总数据库文件的某个百分比。
I know how to determine the size of the database file -- I do it by executing
我知道如何确定数据库文件的大小——我通过执行
PRAGMA PAGE_SIZE;
and
和
PRAGMA PAGE_COUNT;
in two separate queries and multiplying the two to get the file size in bytes. Yes, I know I can just take the size of the file in bytes, but this is similar to the way I've done it in other databases and I want to stick with it, at least for now.
在两个单独的查询中并将两者相乘以获得以字节为单位的文件大小。是的，我知道我只能以字节为单位获取文件的大小，但这与我在其他数据库中的做法类似，我想坚持使用它，至少现在是这样。
My problem is I don't know how to get the size of a TABLE. There has to be some way to do this. Can anyone point me in the right direction?
我的问题是我不知道如何获得 TABLE 的大小。必须有某种方法来做到这一点。任何人都可以指出我正确的方向吗？
采纳答案by Tony Vitabile
There's no easy way to query the size of a table. So what I ended up doing is coming up with an estimate of the table's size by multiplying the number of rows by an estimate of the row's size. I manually summed the length of the integer columns (4 bytes each), plus the sum of the length of the long columns (8 bytes each), plus an estimate of the average length of the string columns using a query. Something like this:
没有简单的方法可以查询表的大小。所以我最终做的是通过将行数乘以行大小的估计来估计表的大小。我手动求和整数列的长度（每个 4 个字节），加上长列的长度总和（每个 8 个字节），加上使用查询对字符串列的平均长度的估计。像这样的东西：
SELECT COUNT(*) * -- The number of rows in the table
( 24 + -- The length of all 4 byte int columns
12 + -- The length of all 8 byte int columns
128 ) -- The estimate of the average length of all string columns
FROM MyTable
The only problems with this are that:
唯一的问题是：
- It will overestimate the size of any row with an integer or long column that can be null, and that happens to be null
- It will overestimate or underestimate the length of the string columns.
- It does not take into account the size of the indexes.
- 它会高估任何具有可以为空的整数或长列的行的大小，而恰好为空
- 它会高估或低估字符串列的长度。
- 它不考虑索引的大小。
This was good enough for our implementation. You might be able to do a better job computing the table's size with a more complicated query that takes nulls & the actual length of the string columns into account.
这对于我们的实施来说已经足够了。您可以使用更复杂的查询来更好地计算表的大小，该查询将空值和字符串列的实际长度考虑在内。
回答by Thomas
You can use the sqlite3_analyzertoolfor this, which can be downloaded here. Just run it on your database and it will spit out a lot of interesting statistics, in a form that can also be piped straight into another SQLite database:
您可以为此使用该sqlite3_analyzer工具，该工具可在此处下载。只需在您的数据库上运行它，它就会输出许多有趣的统计信息，并且可以直接通过管道传输到另一个 SQLite 数据库中：
$ sqlite3_analyzer my_db.sqlite
回答by Christopher K.
If SQLite is compiled with SQLITE_ENABLE_DBSTAT_VTAB, you can query the dbstattable. This should return the table size of table TABLENAME:
如果 SQLite 是用 编译的SQLITE_ENABLE_DBSTAT_VTAB，则可以查询dbstat表。这应该返回 table 的表大小TABLENAME：
SELECT SUM("pgsize") FROM "dbstat" WHERE name='TABLENAME';
https://www.sqlite.org/dbstat.html
https://www.sqlite.org/dbstat.html
This is what is used by the sqlite3_anazlyerCLI tool, which can also be used for this purpose.
这是sqlite3_anazlyerCLI 工具所使用的，也可以用于此目的。
