像许多其他人一样，我很难用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

像@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.

像许多其他人一样，我很难用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

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

添加此过程后运行:

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

下面的代码为所有故事生成选择查询。只需删除最后的“UNION ALL”选择所有结果，并粘贴一个新的查询窗口运行。

SELECT 
concat('select ''', table_name ,''' as TableName, COUNT(*) as RowCount from ' , table_name , ' UNION ALL ')  as TR FROM
information_schema.tables where 
table_schema = 'Database Name'