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

添加此过程后运行:

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

简单的方法:

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 |
+----------------+-----------------+

像许多其他人一样，我很难用InnoDB在INFORMATION_SCHEMA表上获得准确的值，并且能够通过count()进行查询将无限受益，并且希望在一次查询中完成它。

首先，确保启用大规模group_concats:

SET SESSION group_concat_max_len = 1000000;

然后运行此查询以获得将为数据库运行的结果查询。

SELECT CONCAT('SELECT ', GROUP_CONCAT(table1.count SEPARATOR ',\n')) FROM (
    SELECT concat('(SELECT count(id) AS \'',table_name,' Count\' ','FROM ',table_name,') AS ',table_name,'_Count') AS 'count'
    FROM information_schema.tables 
    WHERE table_schema = '**YOUR_DATABASE_HERE**'
) AS table1

这将生成诸如…

SELECT (SELECT count(id) AS 'table1 Count' FROM table1) AS table1_Count,
   (SELECT count(id) AS 'table2 Count' FROM table2) AS table2_Count,
   (SELECT count(id) AS 'table3 Count' FROM table3) AS table3_Count;

这反过来又产生了以下结果:

*************************** 1. row ***************************
table1_Count: 1
table2_Count: 1
table3_Count: 0

如果需要精确的数字，请使用下面的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(*)来获得精确的计数(成本更高)。

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

这就是你所需要的。