MySQL - 如何在一个查询中计算每个表的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2692340/
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
MySQL - How to count all rows per table in one query
提问by Lee
Is there a way to query the DB to find out how many rows there are in all the tables?
有没有办法查询数据库以找出所有表中有多少行？
i.e.
IE
table1 1234
table2 222
table3 7888
Hope you can advise
希望你能指教
回答by great_llama
SELECT
TABLE_NAME,
TABLE_ROWS
FROM
`information_schema`.`tables`
WHERE
`table_schema` = 'YOUR_DB_NAME';
回答by ande
The above will give you an approximation, but if you want an exact count, it can be done in two steps. First, execute a query like the following:
上面会给你一个近似值，但如果你想要一个精确的计数，可以分两步完成。首先，执行如下查询：
select concat("select '",table_name,"', count(*) from ",table_name,";")
from `information_schema`.`tables`
WHERE `table_schema` = '[your schema here]';
That will produce a list of SQL statements, one for each table in your database, you can then run to get an exact count.
这将生成一个 SQL 语句列表，数据库中的每个表一个，然后您可以运行以获取准确计数。
回答by petrichi
This will give you the exact Table name and count on a single list
这将为您提供确切的表名称并在单个列表中计数
SELECT CONCAT('SELECT ''',table_name,''', COUNT(*) FROM ', table_name, ' union all')
FROM information_schema.tables WHERE table_schema = 'clw';
回答by tjmcewan
Synthesising the info above and this postinto one set of queries, we get a self-writing query that will give accurate row counts:
将上面的信息和这篇文章合成一组查询，我们得到一个自写查询，它将提供准确的行数：
SET @tableSchema = 'my_schema';
SET SESSION group_concat_max_len = 10000000;
SET @rowCounts = (
SELECT group_concat(CONCAT('SELECT ''',TABLE_NAME,''', COUNT(*) FROM ', TABLE_NAME) SEPARATOR ' union all ')
FROM information_schema.tables WHERE table_schema = @tableSchema
);
PREPARE statement FROM @rowCounts;
EXECUTE statement;
-- don't run dealloc until you've exported your results ;)
DEALLOCATE PREPARE statement;
回答by Nir
SELECT
table_name,
table_rows
FROM
INFORMATION_SCHEMA.TABLES
回答by a1ex07
select sum(cnt) from
(
select count(*) as cnt from table1
union ALL
select count(*) as cnt from table2
union ALL
select count(*) as cnt from table3
)t1
回答by OneSimpleGeek
Probably want this if you just want tables and no views:
如果您只想要表格而没有视图，可能想要这个：
SELECT TABLE_NAME, TABLE_ROWS
FROM `information_schema`.`tables`
WHERE `table_schema` = 'schema'
AND TABLE_TYPE = 'BASE TABLE';
