MySQL 查询数据库下各个表的行数信息

select table_schema,table_name,table_type,table_rows from information_schema.tables where table_schema='mysql';

TABLE_SCHEMA  TABLE_NAME                 TABLE_TYPE  TABLE_ROWS  
------------  -------------------------  ----------  ------------
mysql         columns_priv               BASE TABLE             0
mysql         component                  BASE TABLE             0
mysql         db                         BASE TABLE             2
mysql         default_roles              BASE TABLE             0
mysql         engine_cost                BASE TABLE             2
mysql         func                       BASE TABLE             0
mysql         general_log                BASE TABLE             2
mysql         global_grants              BASE TABLE            28
mysql         gtid_executed              BASE TABLE             0
mysql         help_category              BASE TABLE            44
mysql         help_keyword               BASE TABLE           738
mysql         help_relation              BASE TABLE          1837
mysql         help_topic                 BASE TABLE           835
mysql         innodb_index_stats         BASE TABLE           507
mysql         innodb_table_stats         BASE TABLE            50
mysql         password_history           BASE TABLE             0
mysql         plugin                     BASE TABLE             0
mysql         procs_priv                 BASE TABLE             0
mysql         proxies_priv               BASE TABLE             1
mysql         role_edges                 BASE TABLE             0
mysql         server_cost                BASE TABLE             6
mysql         servers                    BASE TABLE             0
mysql         slave_master_info          BASE TABLE             0
mysql         slave_relay_log_info       BASE TABLE             0
mysql         slave_worker_info          BASE TABLE             0
mysql         slow_log                   BASE TABLE             2
mysql         tables_priv                BASE TABLE             2
mysql         time_zone                  BASE TABLE             0
mysql         time_zone_leap_second      BASE TABLE             0
mysql         time_zone_name             BASE TABLE             0
mysql         time_zone_transition       BASE TABLE             0
mysql         time_zone_transition_type  BASE TABLE             0
mysql         user                       BASE TABLE             4

对于非事务性表，table_rows 这个值是精确的，对于事务性引擎，这个值通常是估算的。例如 MyISAM，存储精确的数目。对于其它存储引擎，比如 InnoDB ，本值是一个大约的数，与实际值相差可达 40 到 50% 。在这些情况下，使用 SELECT COUNT(*) 来获得准确的数目。对于在 information_schema 数据库中的表， Rows 值为 NULL 。

可以使用如下的 SQL 语句来批量统计数据库中的表的行数：

SELECT CONCAT( 'SELECT "', TABLE_NAME, '", COUNT(*) FROM ', TABLE_SCHEMA, '.', TABLE_NAME, ' UNION ALL' )  EXEC_SQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mysql';

把生成的 SQL 语句拷贝出来，并去掉最后的一个 “ UNION ALL ” 就可以执行了。产生的示例 SQL 如下所示：

SELECT "columns_priv", COUNT(*) FROM mysql.columns_priv UNION ALL                            
SELECT "component", COUNT(*) FROM mysql.component UNION ALL                                  
SELECT "db", COUNT(*) FROM mysql.db UNION ALL                                                
SELECT "default_roles", COUNT(*) FROM mysql.default_roles UNION ALL                          
SELECT "engine_cost", COUNT(*) FROM mysql.engine_cost UNION ALL                              
SELECT "func", COUNT(*) FROM mysql.func UNION ALL                                            
SELECT "general_log", COUNT(*) FROM mysql.general_log UNION ALL                              
SELECT "global_grants", COUNT(*) FROM mysql.global_grants UNION ALL                          
SELECT "gtid_executed", COUNT(*) FROM mysql.gtid_executed UNION ALL                          
SELECT "help_category", COUNT(*) FROM mysql.help_category UNION ALL                          
SELECT "help_keyword", COUNT(*) FROM mysql.help_keyword UNION ALL                            
SELECT "help_relation", COUNT(*) FROM mysql.help_relation UNION ALL                          
SELECT "help_topic", COUNT(*) FROM mysql.help_topic UNION ALL                                
SELECT "innodb_index_stats", COUNT(*) FROM mysql.innodb_index_stats UNION ALL                
SELECT "innodb_table_stats", COUNT(*) FROM mysql.innodb_table_stats UNION ALL                
SELECT "password_history", COUNT(*) FROM mysql.password_history UNION ALL                    
SELECT "plugin", COUNT(*) FROM mysql.plugin UNION ALL                                        
SELECT "procs_priv", COUNT(*) FROM mysql.procs_priv UNION ALL                                
SELECT "proxies_priv", COUNT(*) FROM mysql.proxies_priv UNION ALL                            
SELECT "role_edges", COUNT(*) FROM mysql.role_edges UNION ALL                                
SELECT "server_cost", COUNT(*) FROM mysql.server_cost UNION ALL                              
SELECT "servers", COUNT(*) FROM mysql.servers UNION ALL                                      
SELECT "slave_master_info", COUNT(*) FROM mysql.slave_master_info UNION ALL                  
SELECT "slave_relay_log_info", COUNT(*) FROM mysql.slave_relay_log_info UNION ALL            
SELECT "slave_worker_info", COUNT(*) FROM mysql.slave_worker_info UNION ALL                  
SELECT "slow_log", COUNT(*) FROM mysql.slow_log UNION ALL                                    
SELECT "tables_priv", COUNT(*) FROM mysql.tables_priv UNION ALL                              
SELECT "time_zone", COUNT(*) FROM mysql.time_zone UNION ALL                                  
SELECT "time_zone_leap_second", COUNT(*) FROM mysql.time_zone_leap_second UNION ALL          
SELECT "time_zone_name", COUNT(*) FROM mysql.time_zone_name UNION ALL                        
SELECT "time_zone_transition", COUNT(*) FROM mysql.time_zone_transition UNION ALL            
SELECT "time_zone_transition_type", COUNT(*) FROM mysql.time_zone_transition_type UNION ALL  
SELECT "user", COUNT(*) FROM mysql.user

columns_priv               COUNT(*)  
-------------------------  ----------
columns_priv                        0
component                           0
db                                  2
default_roles                       0
engine_cost                         2
func                                0
general_log                         0
global_grants                      28
gtid_executed                       0
help_category                      44
help_keyword                      711
help_relation                    1635
help_topic                        682
innodb_index_stats                490
innodb_table_stats                 56
password_history                    0
plugin                              0
procs_priv                          0
proxies_priv                        1
role_edges                          0
server_cost                         6
servers                             0
slave_master_info                   0
slave_relay_log_info                0
slave_worker_info                   0
slow_log                            0
tables_priv                         2
time_zone                           0
time_zone_leap_second               0
time_zone_name                      0
time_zone_transition                0
time_zone_transition_type           0
user                                4  

倒序查询数据库各表记录数

USE information_schema;
SELECT table_name,table_rows FROM TABLES WHERE TABLE_SCHEMA = 'mysql' ORDER BY 
table_rows DESC;

TABLE_NAME                 TABLE_ROWS  
-------------------------  ------------
help_relation                      1837
help_topic                          835
help_keyword                        738
innodb_index_stats                  507
innodb_table_stats                   50
help_category                        44
global_grants                        28
server_cost                           6
user                                  4
general_log                           2
slow_log                              2
db                                    2
tables_priv                           2
engine_cost                           2
proxies_priv                          1
slave_master_info                     0
time_zone_transition                  0
password_history                      0
slave_relay_log_info                  0
time_zone_transition_type             0
columns_priv                          0
gtid_executed                         0
plugin                                0
slave_worker_info                     0
component                             0
procs_priv                            0
default_roles                         0
role_edges                            0
time_zone                             0
time_zone_leap_second                 0
func                                  0
servers                               0
time_zone_name                        0

发表评论