mysql–如何从模式的所有表中获取行数(不使用信息模式)

查询应从表名称中收集数据,从information.schema收集模式名称,并且应从实际表中获取行计数.

解决方法:

这实际上是一个非常好的问题,因为MyISAM将计数存储在其标题中,而InnoDB需要完整计数(请参阅我对Why doesn’t InnoDB store the row count?的回答)

解

使用INFORMATION_SCHEMA.TABLES创建用于计算每个表的SQL

所有数据库

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('SELECT ',QUOTE(db),' table_schema,',QUOTE(tb),
' table_name,COUNT(1) table_rows FROM `',db,'`.`',tb,'`') SEPARATOR ' UNION ')
INTO @CountSQL
FROM (SELECT table_schema db,table_name tb FROM information_schema.tables WHERE
table_schema NOT IN ('information_schema','performance_schema','mysql')) A;
SELECT @CountSQL\G
PREPARE s FROM @CountSQL; EXECUTE s; DEALLCOATE PREPARE s;


当前数据库

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('SELECT ',QUOTE(db),' table_schema,',QUOTE(tb),
' table_name,COUNT(1) table_rows FROM `',db,'`.`',tb,'`') SEPARATOR ' UNION ')
INTO @CountSQL
FROM (SELECT table_schema db,table_name tb FROM information_schema.tables WHERE
table_schema = DATABASE()) A;
SELECT @CountSQL\G
PREPARE s FROM @CountSQL; EXECUTE s; DEALLOCATE PREPARE s;


特定数据库(如mydata)

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('SELECT ',QUOTE(db),' table_schema,',QUOTE(tb),
' table_name,COUNT(1) table_rows FROM `',db,'`.`',tb,'`') SEPARATOR ' UNION ')
INTO @CountSQL
FROM (SELECT table_schema db,table_name tb FROM information_schema.tables WHERE
table_schema = 'mydata') A;
SELECT @CountSQL\G
PREPARE s FROM @CountSQL; EXECUTE s; DEALLOCATE PREPARE s;


要查看生成的SQL,请运行此命令

SELECT @CountSQL\G


试试看 ！！！