我不知道为什么这么难，但这就是生活。 下面是执行实际计数的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

这是我如何使用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，我只知道一种方法，那就是计数。

我是这样摘土豆的:

# Put this function in your bash and call with:
# rowpicker DBUSER DBPASS DBNAME [TABLEPATTERN]
function rowpicker() {
    UN=$1
    PW=$2
    DB=$3
    if [ ! -z "$4" ]; then
        PAT="LIKE '$4'"
        tot=-2
    else
        PAT=""
        tot=-1
    fi
    for t in `mysql -u "$UN" -p"$PW" "$DB" -e "SHOW TABLES $PAT"`;do
        if [ $tot -lt 0 ]; then
            echo "Skipping $t";
            let "tot += 1";
        else
            c=`mysql -u "$UN" -p"$PW" "$DB" -e "SELECT count(*) FROM $t"`;
            c=`echo $c | cut -d " " -f 2`;
            echo "$t: $c";
            let "tot += c";
        fi;
    done;
    echo "total rows: $tot"
}

我对此没有任何断言，只是说这是一种非常丑陋但有效的方法，可以获得数据库中每个表中存在多少行，而不需要使用表引擎，也不需要拥有安装存储过程的权限，也不需要安装ruby或php。是的，生锈了。是的，这很重要。Count(*)是准确的。

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

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

如果你知道表的数量和它们的名称，并且假设它们每个都有主键，你可以使用交叉连接结合COUNT(distinct [column])来获得来自每个表的行:

SELECT 
   COUNT(distinct t1.id) + 
   COUNT(distinct t2.id) + 
   COUNT(distinct t3.id) AS totalRows
FROM firstTable t1, secondTable t2, thirdTable t3;

下面是一个SQL Fiddle的例子。