MySQL 信息數據庫 information_schema 深度解析：SCHEMATA、TABLES、COLUMNS、TRIGGERS 核心視圖

information_schema 是 MySQL 內建的元數據視圖庫，不佔用實際存儲，提供對數據庫對象（庫、表、列、索引、觸發器、權限等）的只讀、標準化、跨版本穩定的查詢接口。掌握核心視圖，等於掌握了「數據庫的數據庫」。

一、 入口與列表

SHOW DATABASES;                    -- 可見 information_schema
USE information_schema;
SHOW TABLES;                       -- 40+ 視圖

二、 核心視圖詳解

1. SCHEMATA：所有數據庫信息

SELECT * FROM SCHEMATA;
-- 等價於 SHOW DATABASES;
-- 關鍵列：SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME, SQL_PATH

2. TABLES：所有表/視圖信息（最常用）

SELECT 
  TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT,
  TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH,
  INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, CREATE_TIME,
  TABLE_COLLATION, CHECKSUM, CHECK_TIME, CREATE_OPTIONS
FROM TABLES
WHERE TABLE_SCHEMA = 'my_db';

高頻用途：表大小統計（DATA_LENGTH+INDEX_LENGTH）、行數估計、碎片檢測（DATA_FREE）、自增值檢查、字符集合規。

3. COLUMNS：所有列信息

SELECT 
  TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
  COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
  NUMERIC_PRECISION, NUMERIC_SCALE, COLUMN_TYPE, COLUMN_KEY,
  EXTRA, PRIVILEGES, COLUMN_COMMENT
FROM COLUMNS
WHERE TABLE_SCHEMA = 'my_db' AND TABLE_NAME = 'my_table';

高頻用途：Schema 變更對比、自動生成實體類、檢測無註釋列、字符集不一致排查。

4. TRIGGERS：觸發器信息

SELECT 
  TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE,
  ACTION_STATEMENT, ACTION_TIMING, CREATED, SQL_MODE, DEFINER
FROM TRIGGERS
WHERE TRIGGER_SCHEMA = 'my_db';

配合 ROUTINES（存儲過程/函數）、VIEWS、TABLE_CONSTRAINTS、KEY_COLUMN_USAGE（外鍵）、STATISTICS（索引統計）構成完整元數據體系。

三、 實戰組合查詢

1. 庫表大小 Top 20

SELECT TABLE_SCHEMA, TABLE_NAME,
       ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) AS total_mb
FROM TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema','sys')
ORDER BY total_mb DESC LIMIT 20;

2. 找出無主鍵表

SELECT TABLE_SCHEMA, TABLE_NAME
FROM TABLES
WHERE TABLE_TYPE='BASE TABLE'
  AND TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema','sys')
  AND NOT EXISTS (
    SELECT 1 FROM KEY_COLUMN_USAGE k
    WHERE k.TABLE_SCHEMA = TABLES.TABLE_SCHEMA
      AND k.TABLE_NAME = TABLES.TABLE_NAME
      AND k.CONSTRAINT_NAME = 'PRIMARY'
  );

3. 字符集不一致排查

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM TABLES
WHERE TABLE_SCHEMA = 'my_db'
  AND TABLE_COLLATION != 'utf8mb4_general_ci';

四、 版本兼容性提示

• MySQL 5.7 引入 TABLES.TABLE_ROWS 為 BIGINT，5.6 為 INT。
• 8.0 新增 TABLES.TABLESPACE_NAME、COLUMNS.GENERATION_EXPRESSION（虛擬列）。
• 所有視圖遵循 SQL 標準 INFORMATION_SCHEMA，遷移至 MariaDB/Percona 可直接復用查詢。

把 information_schema 當作「數據庫字典 API」，寫腳本、做治理、建目錄，比解析 SHOW 輸出穩健百倍。