mysql 查看某数据库中所有表的行数，information_schema.tables不准确。count(*)拼接准确。

本文主要是介绍mysql 查看某数据库中所有表的行数，information_schema.tables不准确。count(*)拼接准确。，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

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 "report_cert_action_day", count(*) from bigdata_1.report_cert_action_day union all                                          |
| select "report_cert_action_month", count(*) from bigdata_1.report_cert_action_month union all                                      |
| select "report_cert_day", count(*) from bigdata_1.report_cert_day union all                                                        |
| select "report_cert_month", count(*) from bigdata_1.report_cert_month union all                                                    |
+------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> 

对以上输出结果进行修改，如下：

select "report_cert_action_day", count(*) from bigdata_1.report_cert_action_day union all                                          
select "report_cert_action_month", count(*) from bigdata_1.report_cert_action_month union all
select "report_cert_day", count(*) from bigdata_1.report_cert_day union all                                                        
select "report_cert_month", count(*) from bigdata_1.report_cert_month

输出结果如下：
mysql> select "report_cert_action_day", count(*) from bigdata_1.report_cert_action_day union all                                          
    -> select "report_cert_action_month", count(*) from bigdata_1.report_cert_action_month union all
    -> select "report_cert_day", count(*) from bigdata_1.report_cert_day union all                                                        
    -> select "report_cert_month", count(*) from bigdata_1.report_cert_month
    -> ;
+--------------------------+----------+
| report_cert_action_day   | count(*) |
+--------------------------+----------+
| report_cert_action_day   |      168 |
| report_cert_action_month |      131 |
| report_cert_day          |       82 |
| report_cert_month        |       39 |
+--------------------------+----------+
4 rows in set (0.00 sec)

mysql> 

这篇关于mysql 查看某数据库中所有表的行数，information_schema.tables不准确。count(*)拼接准确。的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！