是否有一种方法可以获得MySQL数据库中所有表的行计数,而无需在每个表上运行SELECT count() ?


当前回答

你可以试试这个。这对我来说很好。

SELECT IFNULL(table_schema,'Total') "Database",TableCount 
FROM (SELECT COUNT(1) TableCount,table_schema 
      FROM information_schema.tables 
      WHERE table_schema NOT IN ('information_schema','mysql') 
      GROUP BY table_schema WITH ROLLUP) A;

其他回答

基于上面@Nathan的回答,但不需要“删除最终的联合”,并带有对输出进行排序的选项,我使用以下SQL。它生成另一个SQL语句,然后运行:

select CONCAT( 'select * from (\n', group_concat( single_select SEPARATOR ' UNION\n'), '\n ) Q order by Q.exact_row_count desc') as sql_query
from (
    SELECT CONCAT(
        'SELECT "', 
        table_name, 
        '" AS table_name, COUNT(1) AS exact_row_count
        FROM `', 
        table_schema,
        '`.`',
        table_name, 
        '`'
    ) as single_select
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE table_schema = 'YOUR_SCHEMA_NAME'
      and table_type = 'BASE TABLE'
) Q 

您确实需要一个足够大的group_concat_max_len服务器变量的值,但是从MariaDb 10.2.4开始,它应该默认为1M。

SELECT SUM(TABLE_ROWS) 
     FROM INFORMATION_SCHEMA.TABLES 
     WHERE TABLE_SCHEMA = '{your_db}';

从文档中注意到:对于InnoDB表,行数只是用于SQL优化的粗略估计。您需要使用COUNT(*)来获得精确的计数(成本更高)。

大多数其他答案建议使用INFORMATION_SCHEMA。但是在MySQL 8中它已经不存在了。行数已移动到INFORMATION_SCHEMA.INNODB_TABLESTATS。

你可以用以下方法查询:

SELECT *
FROM information_schema.INNODB_TABLESTATS
WHERE NAME LIKE "YOUR_DB_NAME/%"
ORDER BY NUM_ROWS DESC

请注意,这仍然是一个近似值,像以前一样,不是一个确切的数字。

像@Venkatramanan和其他人一样,我找到了INFORMATION_SCHEMA。TABLES不可靠(使用InnoDB, MySQL 5.1.44),每次运行时给出不同的行数,即使是在静态表上。这里有一种生成大型SQL语句的相对hack(但是灵活/适应性强)的方法,您可以将其粘贴到新的查询中,而不需要安装Ruby宝石之类的东西。

SELECT CONCAT(
    'SELECT "', 
    table_name, 
    '" AS table_name, COUNT(*) AS exact_row_count FROM `', 
    table_schema,
    '`.`',
    table_name, 
    '` UNION '
) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE table_schema = '**my_schema**';

它产生如下输出:

SELECT "func" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.func UNION                         
SELECT "general_log" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.general_log UNION           
SELECT "help_category" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_category UNION       
SELECT "help_keyword" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_keyword UNION         
SELECT "help_relation" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_relation UNION       
SELECT "help_topic" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_topic UNION             
SELECT "host" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.host UNION                         
SELECT "ndb_binlog_index" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.ndb_binlog_index UNION 

复制粘贴,除了最后一个UNION,可以得到漂亮的输出,

+------------------+-----------------+
| table_name       | exact_row_count |
+------------------+-----------------+
| func             |               0 |
| general_log      |               0 |
| help_category    |              37 |
| help_keyword     |             450 |
| help_relation    |             990 |
| help_topic       |             504 |
| host             |               0 |
| ndb_binlog_index |               0 |
+------------------+-----------------+
8 rows in set (0.01 sec)

我不知道为什么这么难,但这就是生活。 下面是执行实际计数的bash脚本。只需将其保存为(例如count_rows.sh),使其可执行(例如chmod 755 count_rows.sh),并运行它(例如。/count_rows.sh)

#!/bin/bash

readarray -t TABLES < <(mysql --skip-column-names -u myuser -pmypassword mydbname -e "show tables")

# now we have an array like:
# TABLES='([0]="customer" [1]="order" [2]="product")'
# You can print out the array with:
#declare -p TABLES


for i in "${TABLES[@]}"
do
    #echo $i
    COUNT=$(mysql --skip-column-names -u username -pmypassword mydbname -e  "select count(*) from $i")
    echo $i : $COUNT
done