如果你使用数据库information_schema，你可以使用下面的mysql代码(where部分使查询不显示行为空值的表):

SELECT TABLE_NAME, TABLE_ROWS
FROM `TABLES`
WHERE `TABLE_ROWS` >=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.

这是我如何使用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.";

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

我不知道为什么这么难，但这就是生活。 下面是执行实际计数的bash脚本。只需将其保存为(例如count_rows.sh)，使其可执行(例如chmod 755 count_rows.sh)，并运行它(例如。/count_rows.sh)

#!/bin/bash

readarray -t TABLES < <(mysql --skip-column-names -u myuser -pmypassword mydbname -e "show tables")

# now we have an array like:
# TABLES='([0]="customer" [1]="order" [2]="product")'
# You can print out the array with:
#declare -p TABLES


for i in "${TABLES[@]}"
do
    #echo $i
    COUNT=$(mysql --skip-column-names -u username -pmypassword mydbname -e  "select count(*) from $i")
    echo $i : $COUNT
done

基于上面@Nathan的回答，但不需要“删除最终的联合”，并带有对输出进行排序的选项，我使用以下SQL。它生成另一个SQL语句，然后运行:

select CONCAT( 'select * from (\n', group_concat( single_select SEPARATOR ' UNION\n'), '\n ) Q order by Q.exact_row_count desc') as sql_query
from (
    SELECT CONCAT(
        'SELECT "', 
        table_name, 
        '" AS table_name, COUNT(1) AS exact_row_count
        FROM `', 
        table_schema,
        '`.`',
        table_name, 
        '`'
    ) as single_select
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE table_schema = 'YOUR_SCHEMA_NAME'
      and table_type = 'BASE TABLE'
) Q 

您确实需要一个足够大的group_concat_max_len服务器变量的值，但是从MariaDb 10.2.4开始，它应该默认为1M。