当前位置：文江博客话题详情

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

发布于 2024-07-09 00:01:57 字数 63 浏览 10 评论 0原文

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

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

哑剧 2024-07-16 00:01:58

你可以试试这个。它对我来说工作得很好。

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;

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;

回复收藏 0 原文

意犹 2024-07-16 00:01:58

以下查询生成一个（另一个）查询，该查询将从 information_schema.tables 中列出的每个模式中获取每个表的 count(*) 值。此处显示的查询的整个结果 - 所有行组合在一起 - 包含以分号结尾的有效 SQL 语句 - 没有悬空的“联合”。通过在下面的查询中使用联合来避免悬空联合。

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

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.

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

回复收藏 0 原文

停顿的约定 2024-07-16 00:01:58

这就是我为获取实际计数所做的事情（不使用模式），

它速度较慢但更准确。

是一个两步过程

获取数据库的表列表。您可以使用
获取它
```
mysql -uroot -p mydb -e“显示表” 
  
```

创建表列表并将其分配给此 bash 脚本中的数组变量来获取它（用单个空格分隔，就像下面的代码一样）

数组=( 表1 表2 表3 ) 

  对于“${array[@]}”中的 i 
  做 
      回声$i 
      mysql -uroot mydb -e“从$i中选择计数（*）” 
  完毕

运行它：
```
chmod +x script.sh;   ./脚本.sh 
  
```

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

回复收藏 0 原文

喜你已久 2024-07-16 00:01:58

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

Like many others, I have difficulty getting an accurate value on the INFORMATION_SCHEMA tables with InnoDB, and would infinitely benefit from being able to make a query that depends on count(), and, hopefully, do it in one, single query.

First, make sure to enable massive group_concats:

SET SESSION group_concat_max_len = 1000000;

Then run this query to get the resultant query you'll run for your database.

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

This will generate output such as...

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;

This in turn gave the following results:

*************************** 1. row ***************************
table1_Count: 1
table2_Count: 1
table3_Count: 0

回复收藏 0 原文

相守太难 2024-07-16 00:01:58

大多数其他答案建议使用 INFORMATION_SCHEMA.TABLES，但在 MySQL 8 中它不再存在。行数已移至 INFORMATION_SCHEMA.INNODB_TABLESTATS。

您可以通过以下方式查询：

SELECT *
FROM information_schema.INNODB_TABLESTATS
WHERE NAME LIKE "YOUR_DB_NAME/%"
ORDER BY NUM_ROWS DESC

请注意，它仍然是像以前一样的近似值，而不是精确的计数。

Most other answers suggest using INFORMATION_SCHEMA.TABLES, but in MySQL 8 it no longer exists. Rows count has been moved to INFORMATION_SCHEMA.INNODB_TABLESTATS.

You can query it with:

SELECT *
FROM information_schema.INNODB_TABLESTATS
WHERE NAME LIKE "YOUR_DB_NAME/%"
ORDER BY NUM_ROWS DESC

Note that it's still an approximation like before, not an exact count.

回复收藏 0 原文

金橙橙 2024-07-16 00:01:58

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

This is how I count TABLES and ALL RECORDS using 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.";

回复收藏 0 原文

带上头具痛哭 2024-07-16 00:01:58

海报想要行数而不计数，但没有指定哪个表引擎。对于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(*) 是准确的。

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

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.

回复收藏 0 原文

我不是你的备胎 2024-07-16 00:01:58

基于@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。

Based on @Nathan's answer above, but without needing to "remove the final union" and with the option to sort the output, I use the following SQL. It generates another SQL statement which then just run:

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

You do need a sufficiently large value of group_concat_max_len server variable but from MariaDb 10.2.4 it should default to 1M.

回复收藏 0 原文

甜宝宝 2024-07-16 00:01:58

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

I don't know why this has to be so hard but that's life.
Here's my bash script that performs actual counts. Just save this as (e.g. count_rows.sh ), make it executable (e.g. chmod 755 count_rows.sh ), and run it (e.g. ./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

回复收藏 0 原文

不语却知心 2024-07-16 00:01:58

这是我的建议，很干净

SELECT table_name, table_rows FROM information_schema.tables where
information_schema.tables.TABLE_SCHEMA="your_schema_name";

This is my proposal, pretty clean

SELECT table_name, table_rows FROM information_schema.tables where
information_schema.tables.TABLE_SCHEMA="your_schema_name";

回复收藏 0 原文

听不够的曲调 2024-07-16 00:01:58

如果您想要确切的数字，请使用以下 ruby 脚本。您需要 Ruby 和 RubyGems。

安装以下 Gems：

gt; gem install dbi
gt; 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

返回命令行：

gt; ruby count_table_records.rb

输出：

Table users has 7328974 rows.

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

Install following Gems:

gt; gem install dbi
gt; gem install dbd-mysql

File: 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:

gt; ruby count_table_records.rb

Output:

Table users has 7328974 rows.

回复收藏 0 原文

您的好友蓝忘机已上羡 2024-07-16 00:01:58

下面的代码生成所有故事的选择查询。只需删除最后一个“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'

The code below generation the select query for all tales. Just delete last "UNION ALL" select all result and paste a new query window to run.

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'

回复收藏 0 原文

回梦 2024-07-16 00:01:58

如果您知道表的数量及其名称，并假设它们都有主键，则可以将交叉联接与 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 示例。

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:

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.

回复收藏 0 原文

药祭#氼 2024-07-16 00:01:57

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

文档注释：对于 InnoDB 表，<行数只是 SQL 优化中使用的粗略估计。您需要使用 COUNT(*) 进行精确计数（成本更高）。

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

回复收藏 0 原文

高冷爸爸 2024-07-16 00:01:57

您可能可以将一些内容与表格放在一起。我从来没有这样做过，但看起来它有一列用于TABLE_ROWS，一列用于TABLE NAME。

要获取每个表的行，您可以使用如下查询：

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

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.

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

回复收藏 0 原文

浪菊怪哟 2024-07-16 00:01:57

像@Venkatramanan 和其他人一样，我发现 INFORMATION_SCHEMA.TABLES 不可靠（使用 InnoDB、MySQL 5.1.44），每次运行它时都会给出不同的行数，甚至在静态表上也是如此。这是一种相对较老套（但灵活/适应性强）的生成大型 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**';

它产生这样的输出：

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)

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.

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)

回复收藏 0 原文

比忠 2024-07-16 00:01:57

我只是运行：

show table status;

这将为您提供每个表的行数以及一堆其他信息。
我曾经使用上面选定的答案，但这更容易。

我不确定这是否适用于所有版本，但我使用的是带有 InnoDB 引擎的 5.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.

回复收藏 0 原文

好倦 2024-07-16 00:01:57

简单方法：

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

结果示例：

+----------------+-----------------+
| TABLE_NAME     | SUM(TABLE_ROWS) |
+----------------+-----------------+
| calls          |            7533 |
| courses        |             179 |
| course_modules |             298 |
| departments    |              58 |
| faculties      |             236 |
| modules        |             169 |
| searches       |           25423 |
| sections       |             532 |
| universities   |              57 |
| users          |           10293 |
+----------------+-----------------+

Simple way:

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

Result example:

+----------------+-----------------+
| TABLE_NAME     | SUM(TABLE_ROWS) |
+----------------+-----------------+
| calls          |            7533 |
| courses        |             179 |
| course_modules |             298 |
| departments    |              58 |
| faculties      |             236 |
| modules        |             169 |
| searches       |           25423 |
| sections       |             532 |
| universities   |              57 |
| users          |           10293 |
+----------------+-----------------+

回复收藏 0 原文

快乐很简单 2024-07-16 00:01:57

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

这就是你所需要的。

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

That's all you need.

回复收藏 0 原文

痕至 2024-07-16 00:01:57

此存储过程列出表、计算记录数并在最后生成记录总数。

添加此过程后运行它：

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

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

回复收藏 0 原文

我还不会笑 2024-07-16 00:01:57

对于这个估计问题有一些破解/解决方法。

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

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

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)

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

即

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;

自动增量始终会减少 +1 *（表计数）行，但即使有 4,000 个表和 300 万行，其准确度也为 99.9%。比估计的行数好得多。

这样做的好处是，在 Performance_schema 中返回的行计数也会被删除，因为 Maximum 不适用于空值。但是，如果您没有具有自动增量的表，这可能是一个问题。

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.

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.

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.

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.

回复收藏 0 原文

深陷 2024-07-16 00:01:57

要查看当前正在使用的数据库的记录计数：

SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();

To see the record counts for the current DB that's in use:

SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();

回复收藏 0 原文

奢望 2024-07-16 00:01:57

还有一种选择：对于非 InnoDB，它使用 information_schema.TABLES 中的数据（因为它更快），对于 InnoDB - 选择 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;

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

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.

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.

回复收藏 0 原文

如若梦似彩虹 2024-07-16 00:01:57

如果您使用数据库 information_schema，则可以使用以下 mysql 代码（where 部分使查询不显示行具有空值的表）：

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

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

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

回复收藏 0 原文

~没有更多了~

关于作者

用心笑

暂无简介

0 文章

0 评论

23 人气

关注发私信

友情链接

文江博客

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（24）

关于作者

相关话题

热门标签

推荐作者

1CH1MKgiKxn9p

ゞ记忆︶ㄣ

JackDx

信远

yaoduoduo1995

霞映澄塘

友情链接

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（24）

关于作者

相关话题

热门标签

推荐作者

1CH1MKgiKxn9p

ゞ记忆︶ㄣ

JackDx

信远

yaoduoduo1995

霞映澄塘

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。