查询以确定数据库中表的大小？(mysql)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8363435/
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 to determine the size of tables in a database? (mysql)
提问by user784637
The website "How to calculate the MySQL database size"gives two queries:
网站“如何计算MySQL数据库大小”给出了两个查询：
Determine sizes of all databases
确定所有数据库的大小
SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
Determine size of all tables in a database
确定数据库中所有表的大小
SELECT TABLE_NAME, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "schema_name";
The first query works correctly, but the second query doesn't produce a result set. It just shows the names of the fields without any rows. How can I modify the 2nd query to correctly show the size of sizes of my tables in my database.
第一个查询正常工作，但第二个查询不产生结果集。它只显示没有任何行的字段名称。如何修改第二个查询以正确显示数据库中表的大小。
采纳答案by Bill Karwin
Replace "schema_name"with the name of one of yourdatabases.
替换"schema_name"为您的数据库之一的名称。
And use single-quotes for string literals, not double-quotes.
并且对字符串文字使用单引号，而不是双引号。
回答by elrobis
This was a helpful thread. This slight variation on the OP's second recipe returns tables only (no views) and orders the returned dataset by table size---largest-to-smallest:
这是一个有用的线程。OP 的第二个配方的这种轻微变化仅返回表（无视图）并按表大小对返回的数据集进行排序——从大到小：
MySQL:
MySQL：
SELECT
TABLE_NAME, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) 'Size in MB'
FROM information_schema.TABLES
WHERE table_schema = 'yourSchemaName' and TABLE_TYPE='BASE TABLE'
ORDER BY data_length DESC;
PostGRES: I needed to do the same thing in PostGRES today, and borrowing some help from this answer(and another one I lost after closing the browser tab), this is what I ended up with. Adding it here in case it's useful for anyone else.
PostGRES：我今天需要在 PostGRES 中做同样的事情，并从这个答案中借用一些帮助（关闭浏览器选项卡后我丢失了另一个），这就是我最终得到的。在这里添加它以防它对其他人有用。
SELECT
t.tbl table_name,
ct.reltuples row_count,
pg_total_relation_size(t.tbl) size,
pg_size_pretty(pg_total_relation_size(t.tbl)) pretty_size
FROM (
SELECT
table_name tbl
FROM information_schema.tables
WHERE
table_schema = 'public'
AND table_type = 'BASE TABLE'
) t
join (
SELECT
relname, reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
nspname NOT IN ('pg_catalog', 'information_schema')
AND relkind='r'
) ct
on t.tbl = ct.relname
order by size desc ;
回答by elrobis
I Think this is better .. http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html
我认为这更好.. http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html
show table status from database;
回答by Scott S
Here is a Query I use to give the size of each table in a database.
这是我用来给出数据库中每个表的大小的查询。
SELECT CASE WHEN (GROUPING(sob.name)=1) THEN 'All_Tables'
ELSE ISNULL(sob.name, 'unknown') END AS Table_name,
SUM(sys.length) AS Byte_Length
FROM sysobjects sob, syscolumns sys
WHERE sob.xtype='u' AND sys.id=sob.id
GROUP BY sob.name
WITH CUBE
