如何统计Mysql精确的记录数

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 ;