MySQL:在单个查询中汇总所有表的行数 [英] MySQL: Summarize all table row-counts in a single query

问题描述

在查看数据库时，获得所有表(包括它们的行数)的概览非常有用:

When reviewing a database, it's highly useful to get an overview of all the tables, including their row counts:

TableName    Count
t1           1234
t2             37 
...           ...

information_schema数据库中的MySQL TABLES表提供了一个table_rows字段:

The MySQL TABLES table in the information_schema database provides a table_rows field:

SELECT table_name, table_rows
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = '<your db>';

但是table_rows仅对某些数据库引擎有效，而对于INNODB，它要么为NULL，要么不正确.

But table_rows is only valid for some database engines, whereas for INNODB it is either NULL or not accurate.

因此，有必要组成一个对每个表执行显式SELECT Count(*)...的方法.

Hence it's necessary to compose a method that does an explicit SELECT Count(*)... for each table.

在这个关于stackoverflow的问题的许多重复中，有许多答案涉及两步过程.一个查询将创建一个结果集，该结果集的行包含单独的select count(*)语句，然后执行文本编辑过程将其转换为可以产生所需输出的实际语句.

Across many repetitions of this question on stackoverflow, there are numerous answers that involve a two-step process. One query to create a result set with rows containing the individual select count(*) statements, followed by a textediting procedure to turn that into the actual statement that can produce the desired output.

我没有看到这变成一个单一的步骤，因此下面我发布该答案.这不是火箭科学，但将其阐明很方便.

I had not seen this turned into a single step, so below I post that answer. It's not rocket science, but it's convenient to have it spelled out.

推荐答案

这里的第一个示例代码是一个存储过程，对于用户而言，它一步一步地完成了整个过程.

The first example code here is a stored procedure which performs the entire process in one step, so far as the user is concerned.

BEGIN

# zgwp_tables_rowcounts
# TableName RowCount
# Outputs a result set listing all tables and their row counts 
# for the current database

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SET @dbname = DATABASE();

SELECT
  GROUP_CONCAT( 
    CONCAT (
      'SELECT ''',table_name,''' as TableName, COUNT(*) as RowCount FROM ', 
       table_name, ' '
    ) 
    SEPARATOR 'UNION '  
  ) AS Qry
FROM
  information_schema.`TABLES` AS t
WHERE
  t.TABLE_SCHEMA = @dbname AND
  t.TABLE_TYPE = "BASE TABLE"
ORDER BY
  t.TABLE_NAME ASC

INTO @sql
;

PREPARE stmt FROM @sql;

EXECUTE stmt;

END

注意:

• SELECT..INTO @sql创建必要的查询，然后PREPARE ... EXECUTE运行它.

• The SELECT..INTO @sql creates the necessary query, and the PREPARE... EXECUTE runs it.

设置group_concat_max_len变量，以允许GROUP_CONCAT提供足够长的结果字符串.

Sets the group_concat_max_len variable in order to allow a long enough result string from GROUP_CONCAT.

上面的过程对于在Navicat之类的管理环境中或在命令行上快速浏览很有用.但是，尽管返回了一个结果集，据我所知它不能在另一个视图或查询中引用，大概是因为MySQL无法在运行它之前确定它产生的结果集，以及它们具有的列

The above procedure is useful for a quick look in an admin environment like Navicat, or on the command line. However, despite returning a result set, so far as I am aware it can't be referenced in another View or Query, presumably because MySQL is unable to determine, before running it, what result sets it produces, let along what columns they have.

因此，无需人工编辑即可快速生成单独的SELECT ... UNION语句(可用作视图)仍然很有用.如果要将行计数与另一个表中的其他每个表信息结合起来，这很有用.附带另一个存储过程:

So, it is still useful to be able to quickly produce, without manual editing, the separate SELECT...UNION statement that can be used as a View. That is useful if you want to join the row counts to some other per-table info from another table. Herewith another stored procedure:

BEGIN

# zgwp_tables_rowcounts_view_statement
# Output: SelectStatement
# Outputs a single row and column, containing a (possibly lengthy)
# SELECT...UNION statement that, if used as a View, will output
# TableName RowCount for all tables in the current database.

SET SESSION group_concat_max_len = 1000000;
SET @dbname = DATABASE();

SELECT
  GROUP_CONCAT( 
    CONCAT (
      'SELECT ''',table_name,''' as TableName, COUNT(*) as RowCount FROM ', 
      table_name, ' ', CHAR(10)) 
    SEPARATOR 'UNION '  
  ) AS SelectStatement
FROM
  information_schema.`TABLES` AS t
WHERE
  t.TABLE_SCHEMA = @dbname AND
  t.TABLE_TYPE = "BASE TABLE"
ORDER BY
  t.TABLE_NAME ASC
;
END

注释

• 非常类似于概念上的第一个过程.我在每个子"SELECT ... UNION"语句中添加了一个换行符(CHAR(10))，以方便查看或编辑该语句.

• Very similar to the first procedure in concept. I added a linebreak (CHAR(10)) to each subsidiary "SELECT...UNION" statement, for convenience in viewing or editing the statement.

如果对您的环境更方便，则可以将其创建为函数并返回SelectStatement.

You could create this as a function and return the SelectStatement, if that's more convenient for your environment.

希望有帮助.

这篇关于MySQL:在单个查询中汇总所有表的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！