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

Is there a way to get the count of rows in all tables in a MySQL database without running a SELECT count() on each table?

是否有一种方法可以在MySQL数据库中获取所有表中的行数，而不需要在每个表上运行SELECT count() ?

16 个解决方案

#1

320

SELECT SUM(TABLE_ROWS) 
     FROM INFORMATION_SCHEMA.TABLES 
     WHERE TABLE_SCHEMA = '{your_db}';

Note from the docs though: For InnoDB tables, the row count is only a rough estimate used in SQL optimization. You'll need to use COUNT(*) for exact counts (which is more expensive).

但是，从文档中可以看到:对于InnoDB表，行计数只是SQL优化中使用的粗略估计。你需要使用COUNT(*)来精确计数(这更贵)。

#2

138

You can probably put something together with Tables table. I've never done it, but it looks like it has a column for TABLE_ROWS and one for TABLE NAME.

你可以把一些东西和表格放在一起。我从来没有这样做过，但是看起来它有一个TABLE_ROWS的列和一个表名。

To get rows per table, you can use a query like this:

要获得每个表的行，可以使用如下的查询:

SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '**YOUR SCHEMA**';

#3

Like @Venkatramanan and others I found INFORMATION_SCHEMA.TABLES unreliable (using InnoDB, MySQL 5.1.44), giving different row counts each time I run it even on quiesced tables. Here's a relatively hacky (but flexible/adaptable) way of generating a big SQL statement you can paste into a new query, without installing Ruby gems and stuff.

就像@Venkatramanan和其他人一样，我找到了INFORMATION_SCHEMA。表不可靠(使用InnoDB, MySQL 5.1.44)，每当我在quiesced表上运行时，就会给出不同的行计数。这里有一种相对粗糙的(但灵活的/可适应的)方法来生成一个大的SQL语句，您可以将它粘贴到一个新的查询中，而不需要安装Ruby gems和其他东西。

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**';

It produces output like this:

它产生这样的输出:

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

Copy and paste except for the last UNION to get nice output like,

复制粘贴，除非是最后一个结合得到很好的输出，

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

#4

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引擎。

#5

This stored procedure lists tables, counts records, and produces a total number of records at the end.

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

To run it after adding this procedure:

在添加这个过程之后运行它:

CALL `COUNT_ALL_RECORDS_BY_TABLE` ();

- - - - - -

The Procedure:

过程:

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

#6

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

That's all you need.

这是你所需要的。

#7

If you use the database information_schema, you can use this mysql code (the where part makes the query not show tables that have a null value for rows):

如果您使用数据库information_schema，您可以使用这个mysql代码(where部分使查询不显示具有空值的表):

SELECT TABLE_NAME, TABLE_ROWS
FROM `TABLES`
WHERE `TABLE_ROWS` >=0

#8

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;

#9

There's a bit of a hack/workaround to this estimate problem.

对于这个估计问题，有一些窍门。

Auto_Increment - for some reason this returns a much more accurate row count for your database if you have auto increment set up on tables.

Auto_Increment——由于某些原因，如果您在表上设置了自动增量，这将为您的数据库返回更精确的行计数。

Found this when exploring why show table info did not match up with the actual data.

在探究为什么显示表信息与实际数据不匹配时发现了这一点。

SELECT
table_schema 'Database',
SUM(data_length + index_length) AS 'DBSize',
SUM(TABLE_ROWS) AS DBRows,
SUM(AUTO_INCREMENT) AS DBAutoIncCount
FROM information_schema.tables
GROUP BY table_schema;


+--------------------+-----------+---------+----------------+
| Database           | DBSize    | DBRows  | DBAutoIncCount |
+--------------------+-----------+---------+----------------+
| Core               |  35241984 |   76057 |           8341 |
| information_schema |    163840 |    NULL |           NULL |
| jspServ            |     49152 |      11 |            856 |
| mysql              |   7069265 |   30023 |              1 |
| net_snmp           |  47415296 |   95123 |            324 |
| performance_schema |         0 | 1395326 |           NULL |
| sys                |     16384 |       6 |           NULL |
| WebCal             |    655360 |    2809 |           NULL |
| WxObs              | 494256128 |  530533 |        3066752 |
+--------------------+-----------+---------+----------------+
9 rows in set (0.40 sec)

You could then easily use PHP or whatever to return the max of the 2 data columns to give the "best estimate" for row count.

然后，您可以轻松地使用PHP或其他任何方法来返回两个数据列的最大值，以给出行计数的“最佳估计值”。

i.e.

即。

SELECT
table_schema 'Database',
SUM(data_length + index_length) AS 'DBSize',
GREATEST(SUM(TABLE_ROWS), SUM(AUTO_INCREMENT)) AS DBRows
FROM information_schema.tables
GROUP BY table_schema;

Auto Increment will always be +1 * (table count) rows off, but even with 4,000 tables and 3 million rows, that's 99.9% accurate. Much better than the estimated rows.

自动增量将永远是+1 *(表计数)行，但是即使有4,000个表和300万行，也就是99.9%的精度。比估计的行好多了。

The beauty of this is that the row counts returned in performance_schema are erased for you, as well, because greatest does not work on nulls. This may be an issue if you have no tables with auto increment, though.

这样做的好处是，您也会删除performance ce_schema中返回的行计数，因为最伟大的行不适用于nulls。不过，如果没有自动增量表，这可能是一个问题。

#10

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. The entire result of the query shown here - all rows taken together - comprise a valid SQL statement ending in a semicolon - no dangling 'union'. The dangling union is avoided by use of a union in the query below.

下面的查询生成了一个(nother)查询，该查询将从information_schema.tables中列出的每个模式中获取每个表的count(*)值。在这里显示的查询的整个结果——所有的行组合在一起——组成了一个有效的SQL语句，以分号结尾——没有悬挂的“union”。在下面的查询中使用一个联合避免了悬挂的union。

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);';

#11

If you want the exact numbers, use the following ruby script. You need Ruby and RubyGems.

如果您想要确切的数字，请使用下面的ruby脚本。你需要红宝石和红宝石。

Install following Gems:

安装下面的宝石:

$> gem install dbi
$> gem install dbd-mysql

File: count_table_records.rb

文件: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

Go back to the command-line:

回到命令行:

$> ruby count_table_records.rb

Output:

输出:

Table users has 7328974 rows.

#12

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.";

#13

Poster wanted row counts without counting, but didn't specify which table engine. With InnoDB, I only know one way, which is to count.

海报希望行数不计数，但没有指定哪个表引擎。对于InnoDB，我只知道一种方法，那就是计数。

This is how I pick my potatoes:

我就是这样摘土豆的:

# 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"
}

I am making no assertions about this other than that this is a really ugly but effective way to get how many rows exist in each table in the database regardless of table engine and without having to have permission to install stored procedures, and without needing to install ruby or php. Yes, its rusty. Yes it counts. count(*) is accurate.

我对其他没有断言,这是一个非常丑陋的但有效的方式存在于数据库中的每个表多少行不管表引擎和不允许安装存储过程,而无需安装ruby和php。是的,生锈的。是的,这很重要。count(*)是准确的。

#14

This is what I do to get the actual count (no using the schema)

这就是我要获取实际计数的方法(不使用模式)

It's slower but more accurate.

它更慢，但更准确。

It's a two step process at

这是一个两步的过程。

Get list of tables for your db. You can get it using

为您的数据库获取表的列表。你可以使用它。
```
mysql -uroot -p mydb -e "show tables"
```
Create and assign the list of tables to the array variable in this bash script (separated by a single space just like in the code below)

在这个bash脚本中创建并分配表的列表到数组变量(与下面代码中的一个空格分开)
```
array=( table1 table2 table3 )

for i in "${array[@]}"
do
    echo $i
    mysql -uroot mydb -e "select count(*) from $i"
done
```
Run it:

运行该程序:
```
chmod +x script.sh; ./script.sh
```

#15

One more option: for non InnoDB it uses data from information_schema.TABLES (as it's faster), for InnoDB - select count(*) to get the accurate count. Also it ignores views.

还有一个选项:对于非InnoDB，它使用information_schema的数据。表(因为它更快)，对于InnoDB - select count(*)来获得准确的计数。也忽略了的观点。

SET @table_schema = DATABASE();
-- or SET @table_schema = 'my_db_name';

SET GROUP_CONCAT_MAX_LEN=131072;
SET @selects = NULL;

SELECT GROUP_CONCAT(
        'SELECT "', table_name,'" as TABLE_NAME, COUNT(*) as TABLE_ROWS FROM `', table_name, '`'
        SEPARATOR '\nUNION\n') INTO @selects
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = @table_schema
        AND ENGINE = 'InnoDB'
        AND TABLE_TYPE = "BASE TABLE";

SELECT CONCAT_WS('\nUNION\n',
  CONCAT('SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND ENGINE <> "InnoDB" AND TABLE_TYPE = "BASE TABLE"'),
  @selects) INTO @selects;

PREPARE stmt FROM @selects;
EXECUTE stmt USING @table_schema;
DEALLOCATE PREPARE stmt;

If your database has a lot of big InnoDB tables counting all rows can take more time.

如果您的数据库有很多大型的InnoDB表，那么计算所有行可能需要花费更多的时间。

#16

-2

If you know the number of tables and their names, and assuming they each have primary keys, you can use a cross join in combination with COUNT(distinct [column]) to get the rows that come from each table:

如果您知道表的数量和它们的名称，并且假设它们都有主键，那么您可以使用一个交叉连接与COUNT(不同的[列])组合，以得到来自每个表的行:

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

Here is an SQL Fiddle example.

下面是一个SQL小提琴示例。

#1

320

SELECT SUM(TABLE_ROWS) 
     FROM INFORMATION_SCHEMA.TABLES 
     WHERE TABLE_SCHEMA = '{your_db}';