點擊下載Navicat for MySQL

在上周的在 MySQL 中獲取高級的行計數(shù)（第 2 部分）文章中，我們使用了原生的 COUNT() 函數(shù)來計算唯一值以及滿足條件的值。在今天的最后一部分中，我們將學習如何從數(shù)據(jù)庫或整個模式中的所有表中獲取行計數(shù)。

查詢 information_schema 數(shù)據(jù)庫

你不必對每個表運行計數(shù)查詢以獲取行數(shù)。如果你計劃多次運行它，這將是繁瑣的并且可能需要外部腳本。

INFORMATION_SCHEMA 數(shù)據(jù)庫是每個 MySQL 實例存儲有關 MySQL 服務器維護的所有其他數(shù)據(jù)庫的信息的位置。它有時也稱為數(shù)據(jù)字典和系統(tǒng)目錄，是查找有關數(shù)據(jù)庫、表、列的數(shù)據(jù)類型或訪問權限的信息的理想位置。

INFORMATION_SCHEMA“TABLES”表提供有關數(shù)據(jù)庫中還有哪些其他表的信息。通過查詢“TABLES”表，你可以使用單個查詢獲得精確的行計數(shù)。

一個數(shù)據(jù)庫的表計數(shù)

 獲取一個數(shù)據(jù)庫的行計數(shù)很容易。只需添加一個 WHERE 子句，其條件是 table_schema 列與你的數(shù)據(jù)庫名匹配：

SELECT
TABLE_NAME,
TABLE_ROWS
FROM
`information_schema`.`tables`
WHERE
`table_schema` = 'YOUR_DB_NAME';

整個模式的表計數(shù)

獲取模式中所有數(shù)據(jù)庫的行數(shù)需要更多的工作量。為此，我們必須使用已準備的語句。

 在語句中，group_concat() 函數(shù)將多行打包成一個字符串，以便將表名列表轉換為由聯(lián)合連接的多個計數(shù)的一個字符串。

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;

我們的串聯(lián) select 語句保存在 @sql 變量中，以便我們可以將它作為已準備的語句運行：

關于速度和準確性

這些查詢在 MyISAM 表上運行得非常快，并生成非常精確的結果。但是，如 InnoDB 之類的事務存儲引擎不會在表中保留內部的行計數(shù)。而是，在表中采樣許多隨機頁，然后估計整個表的總行數(shù)。MVCC（一種允許并發(fā)訪問行的功能）的后果是，在任何一個時間點，都會有多個版本的行。因此，實際的 count(1) 將取決于事務開始的時間及其隔離級別。像 InnoDB 這樣的事務存儲引擎，你可以預料其計數(shù)的準確性為實際行數(shù)的 ±4% 以內。