MySQL-如何在一个查询中计算每个表中的所有行

有没有一种方法可以查询数据库来找出所有表中有多少行？

也就是说。

table1 1234
table2 222
table3 7888

希望你能给点建议

73012 次浏览
小开 
SELECT
table_name,
table_rows
FROM
INFORMATION_SCHEMA.TABLES
小开
最佳答案 
SELECT
TABLE_NAME,
TABLE_ROWS
FROM
`information_schema`.`tables`
WHERE
`table_schema` = 'YOUR_DB_NAME';
小开 
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
小开

上面给出了一个近似值，但是如果你想要一个精确的计数，可以分两个步骤来完成。首先，执行如下查询:

select concat("select '",table_name,"', count(*) from ",table_name,";")
from `information_schema`.`tables`
WHERE `table_schema` = '[your schema here]';

这将生成一个 SQL 语句列表，对应于数据库中的每个表，然后可以运行以获得精确的计数。

小开

这将为您提供精确的 Table 名称并计算单个列表的数量

SELECT CONCAT('SELECT ''',table_name,''', COUNT(*) FROM ', table_name, ' union all')
FROM information_schema.tables WHERE table_schema = 'clw';
小开

将上面的信息和 这篇文章合成为一组查询，我们得到一个自动编写的查询，它将提供准确的行数:

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;
小开

如果你只想要桌子而没有视图，你可能会想要这个:

SELECT TABLE_NAME, TABLE_ROWS
FROM   `information_schema`.`tables`
WHERE  `table_schema` = 'schema'
AND TABLE_TYPE = 'BASE TABLE';
小开

使用存储过程获取表的行非常方便，例如:

CALL database_tables_row_count('my_shop_db');

展示:

+-------------------+-----------+
| table             | row_count |
+-------------------+-----------+
| user              |         5 |
| payment           |        12 |
+-------------------+-----------+

如果在“ my _ shop _ db”中没有表，你会得到:

Empty set (0.00 sec)

如果你拼错了数据库名称，你会得到:

ERROR 1049 (42000): Unknown database 'my_so_db'

同样的方式，如果你发出的声明 use non_existing_db;

存储过程必须存储在某处(数据库中)。如果您将它存储到当前数据库中，您将能够以这种方式使用它

CALL database_tables_row_count('my_shop_db');

只要使用存储过程的当前数据库，就可以获得关于任何数据库的结果

由于像表行数这样的查询非常常见，因此您可能希望将该过程存储在公共数据库(一种工具箱)中，例如称为 admin。在新数据库中创建存储过程:

CREATE DATABASE IF NOT EXISTS `admin`;

然后切换到它:

USE `admin`;

并创建存储过程:

DROP PROCEDURE IF EXISTS `database_tables_row_count`;


DELIMITER $$
CREATE PROCEDURE `database_tables_row_count`(IN tableSchema VARCHAR(255))
BEGIN
DECLARE msg VARCHAR(128);


IF (SELECT COUNT(TABLE_NAME) FROM information_schema.tables WHERE table_schema = `tableSchema`) = 0 THEN
SET msg = CONCAT('Unknown database \'', `tableSchema`, '\'');
SIGNAL SQLSTATE '42000' SET MESSAGE_TEXT = msg, MYSQL_ERRNO = 1049;
END IF;


SET SESSION group_concat_max_len = 10000000;
SET @rowCounts = (
SELECT group_concat(CONCAT('SELECT ''',TABLE_NAME,''' AS `table`, COUNT(*) AS `row_count` FROM ', `tableSchema`, '.', TABLE_NAME) SEPARATOR ' union all ')
FROM information_schema.tables WHERE table_schema = `tableSchema`
AND TABLE_TYPE = 'BASE TABLE'
);


IF @rowCounts IS NOT NULL THEN
PREPARE statement FROM @rowCounts;
EXECUTE statement;
DEALLOCATE PREPARE statement;
ELSE
# if no base tables found then return an empty set
select 1 where 0 = 1;
END IF;


END$$
DELIMITER ;

然后使用它，尽管目前的数据库:

CALL admin.database_tables_row_count('my_shop_db');

才能得到结果。

没有必要创建一个单独的数据库来保存这个过程，但是我发现有一个专门的数据库作为一种工具箱是很有用的，所以我不需要为每个开发重新创建过程/视图/函数，在我使用拖放数据库之后..。

你可能需要改一下这句话:

AND TABLE_TYPE = 'BASE TABLE'

致:

AND TABLE_TYPE IN ('BASE TABLE', 'VIEW')

如果希望获取视图的行数。

小开

运行此查询 a 以获得结果，InformationSchema 不会给出正确的结果。

Select group_concat(Query SEPARATOR ' union all ') as Full_Query from (SELECT CONCAT('SELECT ''',table_name,''', COUNT(*) FROM ', table_name) as Query
FROM information_schema.tables) AS T1 into @sql from (select
table_schema db,
table_name tablename from information_schema.tables where table_schema not in
('performance_schema', 'mysql', 'information_schema')) t;

那就快跑

prepare s from @sql; execute s; deallocate prepare s;

提交答案