下載Navicat Premium最新版本

Navicat Premium是一個可連接多種數(shù)據(jù)庫的管理工具，它可以讓你以單一程序同時連接到MySQL、Oracle及PostgreSQL數(shù)據(jù)庫，讓管理不同類型的數(shù)據(jù)庫更加的方便。

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

查詢信息模式數(shù)據(jù)庫

您不必對每個表運(yùn)行計數(shù)查詢來獲取行數(shù)。如果您計劃多次運(yùn)行外部腳本，那么這將是一件乏味的事情，并且可能需要外部腳本。

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

INFORMATION_SCHEMA “TABLES”表提供有關(guān)數(shù)據(jù)庫中其他表的信息。通過查詢它，您可以通過一個查詢獲得精確的行數(shù)。

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

很容易獲得一個數(shù)據(jù)庫的行數(shù)。只需添加一個條件是table_schema列與數(shù)據(jù)庫名稱匹配的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       |
+------------+------------+

整個架構(gòu)的表計數(shù)

為模式中的所有數(shù)據(jù)庫獲取行計數(shù)需要付出更多的努力。為此，我們必須使用一份準(zhǔn)備好的聲明。

在語句中，group_concat（）函數(shù)將多行打包為一個字符串，以便將表名列表轉(zhuǎn)換為由聯(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;

我們連接的select語句保存在@sql變量中，這樣我們就可以將其作為準(zhǔn)備好的語句運(yùn)行：

-- 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       |
+-----+-----------+------------+

關(guān)于速度和準(zhǔn)確性

這些查詢將執(zhí)行得非常快，并在MyISAM表上產(chǎn)生非常精確的結(jié)果。但是，事務(wù)性存儲引擎（如InnoDB）不保留表中的內(nèi)部行數(shù)。相反，事務(wù)性存儲引擎對表中的一些隨機(jī)頁進(jìn)行采樣，然后估計整個表的總行數(shù)。MVCC（一種允許并發(fā)訪問行的特性）的后果是，在任何一個時間點(diǎn)，一行都有多個版本。因此，count(1)將取決于事務(wù)啟動的時間及其隔離級別。在InnoDB這樣的事務(wù)性存儲引擎上，您可以期望計數(shù)精確到實(shí)際行數(shù)的4%以內(nèi)。

購買Navicat Premium正版授權(quán)，請點(diǎn)擊“”喲！