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


当前回答

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

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

其他回答

简单的方法:

SELECT
  TABLE_NAME, SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '{Your_DB}'
GROUP BY TABLE_NAME;

结果示例:

+----------------+-----------------+
| 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 |
+----------------+-----------------+
 SELECT TABLE_NAME,SUM(TABLE_ROWS) 
 FROM INFORMATION_SCHEMA.TABLES 
 WHERE TABLE_SCHEMA = 'your_db' 
 GROUP BY TABLE_NAME;

这就是你所需要的。

你可以用表格把一些东西组合在一起。我从来没有这样做过,但它看起来有一个列用于TABLE_ROWS和一个列用于TABLE NAME。

要获取每个表的行,你可以使用这样的查询:

SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '**YOUR SCHEMA**';

这个存储过程列出表,统计记录,并在最后生成记录的总数。

添加此过程后运行:

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

如果需要精确的数字,请使用下面的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.