获取 MySQL 数据库中所有表的记录数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/286039/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get record counts for all tables in MySQL database
提问by Mark
Is there a way to get the count of rows in all tables in a MySQL database without running a SELECT count()on each table?
有没有办法在不运行SELECT count()每个表的情况下获取 MySQL 数据库中所有表中的行数？
回答by Hates_
SELECT SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '{your_db}';
Note from the docs though:For InnoDB tables, the row count is only a rough estimateused in SQL optimization. You'll need to use COUNT(*) for exact counts (which is more expensive).
但请注意文档：对于 InnoDB 表，行数只是SQL 优化中使用的粗略估计。您需要使用 COUNT(*) 进行精确计数（这更昂贵）。
回答by gpojd
You can probably put something together with Tables table. I've never done it, but it looks like it has a column for TABLE_ROWSand one for TABLE NAME.
您可能可以将一些东西与Tables table放在一起。我从来没有做过，但看起来它有一列用于TABLE_ROWS和一列用于TABLE NAME。
To get rows per table, you can use a query like this:
要获取每个表的行，您可以使用如下查询：
SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '**YOUR SCHEMA**';
回答by Nathan
Like @Venkatramanan and others I found INFORMATION_SCHEMA.TABLES unreliable (using InnoDB, MySQL 5.1.44), giving different row counts each time I run it even on quiesced tables. Here's a relatively hacky (but flexible/adaptable) way of generating a big SQL statement you can paste into a new query, without installing Ruby gems and stuff.
像@Venkatramanan 和其他人一样，我发现 INFORMATION_SCHEMA.TABLES 不可靠（使用 InnoDB，MySQL 5.1.44），即使在静默表上运行它时，也会给出不同的行数。这是一种生成大 SQL 语句的相对 hacky（但灵活/适应性强）的方法，您可以将其粘贴到新查询中，而无需安装 Ruby gems 和其他东西。
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**';
It produces output like this:
它产生这样的输出：
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
Copy and paste except for the last UNION to get nice output like,
除了最后一个 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)
回答by djburdick
I just run:
我只是运行：
show table status;
This will give you the row count for EVERY table plus a bunch of other info. I used to use the selected answer above, but this is much easier.
这将为您提供每个表的行数以及一堆其他信息。我曾经使用上面选择的答案，但这要容易得多。
I'm not sure if this works with all versions, but I'm using 5.5 with InnoDB engine.
我不确定这是否适用于所有版本，但我使用的是带有 InnoDB 引擎的 5.5。
回答by Gustavo Castro
SELECT TABLE_NAME,SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db'
GROUP BY TABLE_NAME;
That's all you need.
这就是你所需要的。
回答by Jake Drew
This stored procedure lists tables, counts records, and produces a total number of records at the end.
此存储过程列出表、计算记录并在最后生成总记录数。
To run it after adding this procedure:
添加此过程后运行它：
CALL `COUNT_ALL_RECORDS_BY_TABLE` ();
-
——
The Procedure:
步骤：
DELIMITER $$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `COUNT_ALL_RECORDS_BY_TABLE`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE TNAME CHAR(255);
DECLARE table_names CURSOR for
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN table_names;
DROP TABLE IF EXISTS TCOUNTS;
CREATE TEMPORARY TABLE TCOUNTS
(
TABLE_NAME CHAR(255),
RECORD_COUNT INT
) ENGINE = MEMORY;
WHILE done = 0 DO
FETCH NEXT FROM table_names INTO TNAME;
IF done = 0 THEN
SET @SQL_TXT = CONCAT("INSERT INTO TCOUNTS(SELECT '" , TNAME , "' AS TABLE_NAME, COUNT(*) AS RECORD_COUNT FROM ", TNAME, ")");
PREPARE stmt_name FROM @SQL_TXT;
EXECUTE stmt_name;
DEALLOCATE PREPARE stmt_name;
END IF;
END WHILE;
CLOSE table_names;
SELECT * FROM TCOUNTS;
SELECT SUM(RECORD_COUNT) AS TOTAL_DATABASE_RECORD_CT FROM TCOUNTS;
END
回答by Eduardo Cuomo
Simple way:
简单的方法：
SELECT
TABLE_NAME, SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '{Your_DB}'
GROUP BY TABLE_NAME;
Result example:
结果示例：
+----------------+-----------------+
| TABLE_NAME | SUM(TABLE_ROWS) |
+----------------+-----------------+
| calls | 7533 |
| courses | 179 |
| course_modules | 298 |
| departments | 58 |
| faculties | 236 |
| modules | 169 |
| searches | 25423 |
| sections | 532 |
| universities | 57 |
| users | 10293 |
+----------------+-----------------+
回答by user3260912
There's a bit of a hack/workaround to this estimate problem.
这个估计问题有一些技巧/解决方法。
Auto_Increment - for some reason this returns a much more accurate row count for your database if you have auto increment set up on tables.
Auto_Increment - 出于某种原因，如果您在表上设置了自动增量，则会为您的数据库返回更准确的行数。
Found this when exploring why show table info did not match up with the actual data.
在探索为什么显示表信息与实际数据不匹配时发现了这一点。
SELECT
table_schema 'Database',
SUM(data_length + index_length) AS 'DBSize',
SUM(TABLE_ROWS) AS DBRows,
SUM(AUTO_INCREMENT) AS DBAutoIncCount
FROM information_schema.tables
GROUP BY table_schema;
+--------------------+-----------+---------+----------------+
| Database | DBSize | DBRows | DBAutoIncCount |
+--------------------+-----------+---------+----------------+
| Core | 35241984 | 76057 | 8341 |
| information_schema | 163840 | NULL | NULL |
| jspServ | 49152 | 11 | 856 |
| mysql | 7069265 | 30023 | 1 |
| net_snmp | 47415296 | 95123 | 324 |
| performance_schema | 0 | 1395326 | NULL |
| sys | 16384 | 6 | NULL |
| WebCal | 655360 | 2809 | NULL |
| WxObs | 494256128 | 530533 | 3066752 |
+--------------------+-----------+---------+----------------+
9 rows in set (0.40 sec)
You could then easily use PHP or whatever to return the max of the 2 data columns to give the "best estimate" for row count.
然后，您可以轻松地使用 PHP 或其他任何东西来返回 2 个数据列的最大值，以给出行数的“最佳估计”。
i.e.
IE
SELECT
table_schema 'Database',
SUM(data_length + index_length) AS 'DBSize',
GREATEST(SUM(TABLE_ROWS), SUM(AUTO_INCREMENT)) AS DBRows
FROM information_schema.tables
GROUP BY table_schema;
Auto Increment will always be +1 * (table count) rows off, but even with 4,000 tables and 3 million rows, that's 99.9% accurate. Much better than the estimated rows.
Auto Increment 将始终关闭 +1 *（表计数）行，但即使有 4,000 个表和 300 万行，准确率仍为 99.9%。比估计的行好得多。
The beauty of this is that the row counts returned in performance_schema are erased for you, as well, because greatest does not work on nulls. This may be an issue if you have no tables with auto increment, though.
这样做的好处是，在 performance_schema 中返回的行数也会为您删除，因为 best 不适用于空值。但是，如果您没有带有自动增量的表，这可能是一个问题。
回答by Nimesh07
You can try this. It is working fine for me.
你可以试试这个。它对我来说很好。
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;
回答by Robin Manoli
If you use the database information_schema, you can use this mysql code (the where part makes the query not show tables that have a null value for rows):
如果您使用数据库 information_schema，则可以使用此 mysql 代码（where 部分使查询不显示具有空值行的表）：
SELECT TABLE_NAME, TABLE_ROWS
FROM `TABLES`
WHERE `TABLE_ROWS` >=0
