数据库Navicat for MySQL使用教程：从 MySQL 中的多个表和视图中获取行计数（第 3 部分）

在上周的在 MySQL 中获取高级的行计数（第 2 部分）文章中，我们使用了原生的 COUNT() 函数来计算唯一值以及满足条件的值。在今天的最后一部分中，我们将学习如何从数据库或整个模式中的所有表中获取行计数。

在上周的在 MySQL 中获取高级的行计数（第 2 部分）文章中，我们使用了原生的 COUNT() 函数来计算唯一值以及满足条件的值。在今天的最后一部分中，我们将学习如何从数据库或整个模式中的所有表中获取行计数。

查询 information_schema 数据库

你不必对每个表运行计数查询以获取行数。如果你计划多次运行它，这将是繁琐的并且可能需要外部脚本。

INFORMATION_SCHEMA 数据库是每个 MySQL 实例存储有关 MySQL 服务器维护的所有其他数据库的信息的位置。它有时也称为数据字典和系统目录，是查找有关数据库、表、列的数据类型或访问权限的信息的理想位置。

INFORMATION_SCHEMA“TABLES”表提供有关数据库中还有哪些其他表的信息。通过查询“TABLES”表，你可以使用单个查询获得精确的行计数。

一个数据库的表计数

获取一个数据库的行计数很容易。只需添加一个 WHERE 子句，其条件是 table_schema 列与你的数据库名匹配：

SELECTTABLE_NAME,TABLE_ROWSFROM`information_schema`.`tables`WHERE`table_schema` = 'YOUR_DB_NAME';

+————+————+
| TABLE_NAME | TABLE_ROWS |
+————+————+
| Table1     | 105        |
+————+————+
| Table2     | 10299      |
+————+————+
| Table3     | 0          |
+————+————+
| Table4     | 1045       |
+————+————+

整个模式的表计数

获取模式中所有数据库的行数需要更多的工作量。为此，我们必须使用已准备的语句。

在语句中，group_concat() 函数将多行打包成一个字符串，以便将表名列表转换为由联合连接的多个计数的一个字符串。

Select-- Sort the tables by countconcat('select * from (',-- Aggregate rows into a single string connected by unionsgroup_concat(-- Build a "select count(1) from db.tablename" per tableconcat('select ',quote(db), ' db, ',quote(tablename), ' tablename, ''count(1) "rowcount" ','from ', db, '.', tablename)separator ' union '), ') t order by 3 desc')into @sqlfrom (selecttable_schema db,table_name tablenamefrom information_schema.tableswhere table_schema not in('performance_schema', 'mysql', 'information_schema')) t;

我们的串联 select 语句保存在 @sql 变量中，以便我们可以将它作为已准备的语句运行：

— Execute @sql
prepare s from @sql; execute s; deallocate prepare s;
+—–+———–+————+
| db  | tablename | rowcount   |
+—–+———–+————+
| DB1 | Table1    | 1457       |
+—–+———–+————+
| DB1 | Table2    | 1029       |
+—–+———–+————+
| DB2 | Table1    | 22002      |
+—–+———–+————+
| DB2 | Table2    | 1022       |
+—–+———–+————+

关于速度和准确性

这些查询在 MyISAM 表上运行得非常快，并生成非常精确的结果。但是，如 InnoDB 之类的事务存储引擎不会在表中保留内部的行计数。而是，在表中采样许多随机页，然后估计整个表的总行数。MVCC（一种允许并发访问行的功能）的后果是，在任何一个时间点，都会有多个版本的行。因此，实际的 count(1) 将取决于事务开始的时间及其隔离级别。像 InnoDB 这样的事务存储引擎，你可以预料其计数的准确性为实际行数的 ±4% 以内。

标签：