1.粗略统计(查看状态)
对于InnoDB表,行数只是SQL优化中使用的粗略估计,information_schema是不精确的,sum(table_rows) 和 count(1) 结果会不一样。
# 1.数据库查询总条数
mysql>use information_schema;
mysql>select sum(table_rows) from tables where TABLE_SCHEMA = 'test_db';
# 2.mysql 查看数据库中所有表的记录数
mysql>use information_schema;
mysql>select table_name,table_rows from tables where TABLE_SCHEMA = 'test_db' order by table_rows desc;
2.精确统计(迁移 ,同步)
2.1 快速生成统计SQL
select concat(
'select "',
TABLE_name,
'", count(*) from ',
TABLE_SCHEMA,
'.',
TABLE_name,
' union all'
) from information_schema.tables
where TABLE_SCHEMA='test_db';
2.2 拼接导出结果
-- 去掉最后的union all
SELECT SUM(`COUNT(*)`) FROM (
select "tbl_aa", count(*) from test_db.tbl_aa union all
select "tbl_bb", count(*) from test_db.tbl_bb union all
select "tbl_cc", count(*) from test_db.tbl_cc union all
select "tbl_dd", count(*) from test_db.tbl_dd
) a ;