mysql使用information_schema.tables统计表的行数，统计结果和count(*)的结果不一样。
select table_name,table_rows from information_schema.tables
where TABLE_SCHEMA = 'qyqdb'
order by table_rows desc;
经查询：information_schema.tables 对于InnoDB表，table_rows行计数仅是大概估计值，不准确。
mysql使用select count(*) from table_name可以查询某个表的总记录数。比较准确！
想快速的知道数据库中所有表的记录数信息怎么办？
另外一种办法还是借助information_schema库的tables表，来拼接出一个条sql语句，例如：
统计qyqdb数据库下所有的表的行数，生产统计语句。
select concat(
'select "',
TABLE_name,
'", count(*) from ',
TABLE_SCHEMA,
'.',
TABLE_name,
' union all'
) from information_schema.tables
where TABLE_SCHEMA='qyqdb';
把生成的结果手动加工一下。
举例如下：
统计bigData_1数据库下所有表的行数：
select concat(
'select "',
TABLE_name,
'", count(*) from ',
TABLE_SCHEMA,
'.',
TABLE_name,
' union all'
) from information_schema.tables
where TABLE_SCHEMA in ('bigData_1');
结果：
+------------------------------------------------------------------------------------------------------------------------------------+
| concat(
'select "',
TABLE_name,
'", count(*) from ',
TABLE_SCHEMA,
'.',
TABLE_name,
' union all'
) |
+------------------------------------------------------------------------------------------------------------------------------------+
| select "AA_cert_action_day", count(*) from bigdata_1.AA_cert_action_day union all |
| select "AA_cert_action_month", count(*) from bigdata_1.AA_cert_action_month union all |
| select "AA_cert_day", count(*) from bigdata_1.AA_cert_day union all |
| select "AA_cert_month", count(*) from bigdata_1.AA_cert_month union all |
+------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
mysql>
对以上输出结果进行修改，如下：
select "AA_cert_action_day", count(*) from bigdata_1.AA_cert_action_day union all
select "AA_cert_action_month", count(*) from bigdata_1.AA_cert_action_month union all
select "AA_cert_day", count(*) from bigdata_1.AA_cert_day union all
select "AA_cert_month", count(*) from bigdata_1.AA_cert_month
输出结果如下：
mysql> select "AA_cert_action_day", count(*) from bigdata_1.AA_cert_action_day union all
-> select "AA_cert_action_month", count(*) from bigdata_1.AA_cert_action_month union all
-> select "AA_cert_day", count(*) from bigdata_1.AA_cert_day union all
-> select "AA_cert_month", count(*) from bigdata_1.AA_cert_month
-> ;
+--------------------------+----------+
| report_cert_action_day | count(*) |
+--------------------------+----------+
| AA_cert_action_day | 168 |
| AA_cert_action_month | 131 |
| AA_cert_day | 82 |
| AA_cert_month | 39 |
+--------------------------+----------+
4 rows in set (0.00 sec)
mysql>