您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL information_schema 系統表全解析：元數據查詢實戰速查表

編輯：MySQL 綜合教學

MySQL information_schema 系統表全解析：元數據查詢實戰速查表

information_schema 是 MySQL 元數據的虛擬資料庫，所有 SHOW 指令底層皆查詢此處。掌握核心表結構，能用 SQL 替代 SHOW 實現自動化巡檢、Schema 變更偵測、權限審計、索引分析等進階用法。

核心表速查

表名	用途	對應 SHOW 指令	高頻查詢欄位
SCHEMATA	資料庫列表	SHOW DATABASES	SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME
TABLES	表/視圖資訊	SHOW TABLES	TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_ROWS, CREATE_TIME, TABLE_COLLATION
COLUMNS	欄位定義	SHOW COLUMNS	TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_KEY, IS_NULLABLE, COLUMN_DEFAULT, EXTRA
STATISTICS	索引統計	SHOW INDEX	TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY, NON_UNIQUE
ROUTINES	預存程序/函數	SHOW PROCEDURE/FUNCTION STATUS	ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, DEFINER, CREATED, LAST_ALTERED, SQL_DATA_ACCESS
TRIGGERS	觸發器	SHOW TRIGGERS	TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION
EVENTS	事件調度器	SHOW EVENTS	EVENT_SCHEMA, EVENT_NAME, STATUS, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD
USER_PRIVILEGES	全域權限	SHOW GRANTS	GRANTEE, PRIVILEGE_TYPE, IS_GRANTABLE
SCHEMA_PRIVILEGES	庫級權限	-	GRANTEE, TABLE_SCHEMA, PRIVILEGE_TYPE
TABLE_PRIVILEGES	表級權限	-	GRANTEE, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE
COLUMN_PRIVILEGES	欄位級權限	-	GRANTEE, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, PRIVILEGE_TYPE
CHARACTER_SETS	字符集支援	SHOW CHARACTER SET	CHARACTER_SET_NAME, DEFAULT_COLLATE_NAME, MAXLEN
COLLATIONS	校對規則	SHOW COLLATION	COLLATION_NAME, CHARACTER_SET_NAME, IS_DEFAULT, IS_COMPILED
TABLE_CONSTRAINTS	約束 (PK/FK/UNIQUE/CHECK)	-	CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME
KEY_COLUMN_USAGE	約束涉及欄位	-	CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
VIEWS	視圖定義	SHOW CREATE VIEW	TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION, IS_UPDATABLE
PARTITIONS	分區資訊	-	TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, TABLE_ROWS
REFERENTIAL_CONSTRAINTS	FK 關係細節	-	CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, UPDATE_RULE, DELETE_RULE

實戰查詢範例

-- 1. 找出無主鍵表 (InnoDB 必須有 PK)
SELECT TABLE_SCHEMA, TABLE_NAME
FROM TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
  AND ENGINE = 'InnoDB'
  AND TABLE_NAME NOT IN (
    SELECT TABLE_NAME FROM TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
  );

-- 2. 找出未被任何索引覆蓋的外鍵欄位 (需手動加索引)
SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.COLUMN_NAME, kcu.REFERENCED_TABLE_NAME
FROM KEY_COLUMN_USAGE kcu
JOIN REFERENTIAL_CONSTRAINTS rc ON kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
LEFT JOIN STATISTICS s
  ON s.TABLE_SCHEMA = kcu.TABLE_SCHEMA
  AND s.TABLE_NAME = kcu.TABLE_NAME
  AND s.COLUMN_NAME = kcu.COLUMN_NAME
  AND s.SEQ_IN_INDEX = 1
WHERE s.INDEX_NAME IS NULL;

-- 3. 統計各庫表數量、總行數、資料大小
SELECT TABLE_SCHEMA,
       COUNT(*) AS table_count,
       SUM(TABLE_ROWS) AS total_rows,
       ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024, 2) AS size_mb
FROM TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
GROUP BY TABLE_SCHEMA
ORDER BY size_mb DESC;

-- 4. 權限審計：列出所有擁有 DROP 權限的帳號
SELECT GRANTEE, PRIVILEGE_TYPE
FROM USER_PRIVILEGES
WHERE PRIVILEGE_TYPE IN ('DROP','SHUTDOWN','FILE','PROCESS','SUPER')
ORDER BY GRANTEE;

-- 5. 找出所有包含 utf8mb3 (utf8) 的表/欄位，準備遷移 utf8mb4
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM COLUMNS
WHERE CHARACTER_SET_NAME = 'utf8' AND COLLATION_NAME LIKE 'utf8%'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

效能提示

information_schema 查詢會加 LOCK_open 鎖，大量表時可能阻塞 DDL。生產環境巡檢建議低峰期執行，或用 mysqlpump --metadata-lock=0 導出後離線分析。
TABLE_ROWS 為估算值 (InnoDB 抽樣)，精確行數請 SELECT COUNT(*)。
STATISTICS.CARDINALITY 亦為估算，ANALYZE TABLE 後更新。

結語：information_schema 是 MySQL 元數據的「總匯」。將常用巡檢查詢封裝為 View 或 Stored Procedure，納入 Grafana/Prometheus 監控儀表板，即可實現 Schema 級別的可觀測性與自動化治理。