获取MySQL数据库中所有表的记录计数

本文翻译自：Get record counts for all tables in MySQL database

有没有一种方法可以获取MySQL数据库中所有表的行数，而无需在每个表上运行SELECT count() ？

---

#1楼

参考：https://stackoom.com/question/1CPX/获取MySQL数据库中所有表的记录计数

---

#2楼

This is how I count TABLES and ALL RECORDS using PHP: 这就是我使用PHP计算表和所有记录的方式：

$dtb = mysql_query("SHOW TABLES") or die (mysql_error());
$jmltbl = 0;
$jml_record = 0;
$jml_record = 0;

while ($row = mysql_fetch_array($dtb)) { 
    $sql1 = mysql_query("SELECT * FROM " . $row[0]);            
    $jml_record = mysql_num_rows($sql1);            
    echo "Table: " . $row[0] . ": " . $jml_record record . "<br>";      
    $jmltbl++;
    $jml_record += $jml_record;
}

echo "--------------------------------<br>$jmltbl Tables, $jml_record > records.";

---

#3楼

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

That's all you need. 这就是您所需要的。

---

#4楼

The following query produces a(nother) query that will get the value of count(*) for every table, from every schema, listed in information_schema.tables. 以下查询生成一个（另一个）查询，该查询将从information_schema.tables中列出的每个模式中的每个表获取count（*）的值。 The entire result of the query shown here - all rows taken together - comprise a valid SQL statement ending in a semicolon - no dangling 'union'. 此处显示的查询的整个结果-所有行加在一起-包含以分号结尾的有效SQL语句-无悬挂的“联合”。 The dangling union is avoided by use of a union in the query below. 通过在下面的查询中使用联合，可以避免悬挂的联合。

select concat('select "', table_schema, '.', table_name, '" as `schema.table`,
                          count(*)
                 from ', table_schema, '.', table_name, ' union ') as 'Query Row'
  from information_schema.tables
 union
 select '(select null, null limit 0);';

---

#5楼

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. 我不确定这是否适用于所有版本，但是我将5.5与InnoDB引擎一起使用。

---

#6楼

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;