是否有一种方法可以获得MySQL数据库中所有表的行计数,而无需在每个表上运行SELECT count() ?
当前回答
还有一个选择:对于非InnoDB,它使用information_schema中的数据。TABLES(因为它更快),对于InnoDB -选择count(*)来获得准确的计数。它还会忽略视图。
SET @table_schema = DATABASE();
-- or SET @table_schema = 'my_db_name';
SET GROUP_CONCAT_MAX_LEN=131072;
SET @selects = NULL;
SELECT GROUP_CONCAT(
'SELECT "', table_name,'" as TABLE_NAME, COUNT(*) as TABLE_ROWS FROM `', table_name, '`'
SEPARATOR '\nUNION\n') INTO @selects
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = @table_schema
AND ENGINE = 'InnoDB'
AND TABLE_TYPE = "BASE TABLE";
SELECT CONCAT_WS('\nUNION\n',
CONCAT('SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND ENGINE <> "InnoDB" AND TABLE_TYPE = "BASE TABLE"'),
@selects) INTO @selects;
PREPARE stmt FROM @selects;
EXECUTE stmt USING @table_schema;
DEALLOCATE PREPARE stmt;
如果你的数据库有很多大的InnoDB表,计算所有行会花费更多的时间。
其他回答
这个存储过程列出表,统计记录,并在最后生成记录的总数。
添加此过程后运行:
CALL `COUNT_ALL_RECORDS_BY_TABLE` ();
-
过程:
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
这是我获得实际计数的方法(不使用模式)
它更慢,但更准确。
这个过程有两步
获取数据库的表列表。你可以使用它 Mysql -uroot -p mydb -e“显示表” 在这个bash脚本中创建表列表并将其分配给数组变量(与下面的代码一样,用一个空格分隔) 数组=(table1 table2 table3) ${array[@]}中的I 做 echo $我 Mysql -uroot mydb -e "select count(*) from $i" 完成 运行该程序: Chmod +x script.sh;。/ script.sh
像@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)
如果需要精确的数字,请使用下面的ruby脚本。你需要Ruby和RubyGems。
安装以下Gems:
$> gem install dbi
$> gem install dbd-mysql
文件:count_table_records.rb
require 'rubygems'
require 'dbi'
db_handler = DBI.connect('DBI:Mysql:database_name:localhost', 'username', 'password')
# Collect all Tables
sql_1 = db_handler.prepare('SHOW tables;')
sql_1.execute
tables = sql_1.map { |row| row[0]}
sql_1.finish
tables.each do |table_name|
sql_2 = db_handler.prepare("SELECT count(*) FROM #{table_name};")
sql_2.execute
sql_2.each do |row|
puts "Table #{table_name} has #{row[0]} rows."
end
sql_2.finish
end
db_handler.disconnect
回到命令行:
$> ruby count_table_records.rb
输出:
Table users has 7328974 rows.
SELECT SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '{your_db}';
从文档中注意到:对于InnoDB表,行数只是用于SQL优化的粗略估计。您需要使用COUNT(*)来获得精确的计数(成本更高)。