Navicat使用教程:从MySQL中的多个表和视图中获取行计数(第3部分)

翻译|使用教程|编辑:李爽夏|2019-02-15 09:39:52.000|阅读 174 次

概述:本篇文章介绍了如何从MySQL中的多个表和视图中获取行计数。

相关链接:

下载Navicat Premium最新版本

Navicat Premium是一个可连接多种数据库的管理工具,它可以让你以单一程序同时连接到MySQL、Oracle及PostgreSQL数据库,让管理不同类型的数据库更加的方便。

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

查询信息模式数据库

您不必对每个表运行计数查询来获取行数。如果您计划多次运行外部脚本,那么这将是一件乏味的事情,并且可能需要外部脚本。

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

INFORMATION_SCHEMA “TABLES”表提供有关数据库中其他表的信息。通过查询它,您可以通过一个查询获得精确的行数。

一个数据库的表计数

很容易获得一个数据库的行数。只需添加一个条件是table_schema列与数据库名称匹配的WHERE子句:

SELECT
    TABLE_NAME,
    TABLE_ROWS
FROM
    `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 count
  concat(
    'select * from (',
    -- Aggregate rows into a single string connected by unions
    group_concat(
      -- Build a "select count(1) from db.tablename" per table
      concat('select ',
        quote(db), ' db, ',
        quote(tablename), ' tablename, '
        'count(1) "rowcount" ',
        'from ', db, '.', tablename)
      separator ' union ')
    , ') t order by 3 desc')
into @sql
from (
  select
    table_schema db,
    table_name tablename
  from information_schema.tables
  where 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%以内。

购买Navicat Premium正版授权,请点击“咨询在线客服”哟!

PyCharm


标签:数据库mysqloracle

本站文章除注明转载外,均为本站原创或翻译。欢迎任何形式的转载,但请务必注明出处,尊重他人劳动成果

登录 慧都网发表评论


暂无评论...

为你推荐

  • 推荐视频
  • 推荐活动
  • 推荐产品
  • 推荐文章
  • 慧都慧问
相关厂商
相关产品
Navicat Premium

Navicat Premium 是一套数据库管理工具,让你使用一个程序即可同時连接到 MySQL、MariaDB、SQL Server、SQLite、Oracle 和 PostgreSQL 数据库。

在线
客服
在线
QQ
电话
咨询
400-700-1020
购物车 反馈 返回
顶部
在线客服系统
live chat