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

MySQL 元數據查詢大全：表、視圖、存儲過程、索引、外鍵結構一鍵獲取

編輯：MySQL 綜合教學

MySQL 元數據查詢大全：表、視圖、存儲過程、索引、外鍵結構一鍵獲取

基於 information_schema 系統庫，可通過標準 SQL 查詢獲取 MySQL 實例中所有對象的結構定義，支援自動化文檔生成、代碼生成、異構遷移驗證、Schema 比對等場景。本文匯總生產環境驗證的核心查詢模板，基於示例庫 sakila (MySQL 官方樣本庫) 實測有效。

前置準備

USE sakila;  -- 切換到目標數據庫

1. 表結構查詢

獲取所有基表名稱

SHOW TABLES;
-- 或標準 SQL
SELECT TABLE_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'sakila' AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

獲取指定表列定義 (含類型、長度、空性、註釋)

-- 方法 1: DESC (簡潔)
DESC actor;

-- 方法 2: 標準 SQL (詳盡)
SELECT 
  COLUMN_NAME, 
  DATA_TYPE, 
  CHARACTER_MAXIMUM_LENGTH, 
  NUMERIC_PRECISION, 
  NUMERIC_SCALE, 
  CASE IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END AS IS_NULLABLE,
  COLUMN_TYPE,
  COLUMN_COMMENT,
  COLUMN_KEY,      -- PRI/UNI/MUL
  EXTRA            -- auto_increment 等
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'sakila' AND TABLE_NAME = 'actor'
ORDER BY ORDINAL_POSITION;

2. 視圖結構查詢

獲取所有視圖名稱

SELECT TABLE_NAME, CREATE_TIME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'sakila' AND TABLE_TYPE = 'VIEW'
ORDER BY TABLE_NAME;

獲取視圖列定義

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, 
       NUMERIC_PRECISION, NUMERIC_SCALE, 
       CASE IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END AS IS_NULLABLE,
       COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'sakila' AND TABLE_NAME = 'actor_info'
ORDER BY ORDINAL_POSITION;

獲取視圖定義文本 (DDL)

SELECT VIEW_DEFINITION 
FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_SCHEMA = 'sakila' AND TABLE_NAME = 'actor_info';

3. 索引結構查詢

獲取表索引概覽 (索引名、列數、唯一性、是否主鍵)

SELECT 
  INDEX_NAME, 
  COUNT(*) AS COLUMN_COUNT, 
  MAX(NON_UNIQUE) AS NON_UNIQUE, 
  CASE INDEX_NAME WHEN 'PRIMARY' THEN 1 ELSE 0 END AS IS_PRIMARY
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = 'sakila' AND TABLE_NAME = 'customer'
GROUP BY INDEX_NAME
ORDER BY INDEX_NAME;

獲取索引包含列詳情 (含順序)

SELECT INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = 'sakila' AND TABLE_NAME = 'customer'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

4. 主鍵查詢

SELECT 
  kcu.CONSTRAINT_NAME, 
  kcu.COLUMN_NAME, 
  kcu.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
  ON tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA 
  AND tc.TABLE_NAME = kcu.TABLE_NAME 
  AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE kcu.TABLE_SCHEMA = 'sakila' 
  AND kcu.TABLE_NAME = 'address' 
  AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY kcu.ORDINAL_POSITION;

5. 外鍵查詢 (本表引用他表)

獲取外鍵約束名

SELECT DISTINCT CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_SCHEMA = 'sakila' 
  AND TABLE_NAME = 'customer' 
  AND CONSTRAINT_TYPE = 'FOREIGN KEY';

獲取外鍵列映射詳情 (本表列 → 參考表列)

SELECT 
  kcu.CONSTRAINT_NAME, 
  kcu.COLUMN_NAME, 
  kcu.POSITION_IN_UNIQUE_CONSTRAINT,
  kcu.REFERENCED_TABLE_NAME, 
  kcu.REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
  ON tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA 
  AND tc.TABLE_NAME = kcu.TABLE_NAME 
  AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE kcu.TABLE_SCHEMA = 'sakila' 
  AND kcu.TABLE_NAME = 'customer' 
  AND tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
ORDER BY kcu.CONSTRAINT_NAME, kcu.POSITION_IN_UNIQUE_CONSTRAINT;

6. 反向外鍵查詢 (他表引用本表)

獲取引用本表的外鍵約束名

SELECT DISTINCT CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_SCHEMA = 'sakila' 
  AND REFERENCED_TABLE_NAME = 'customer';

獲取引用詳情 (引用表、引用列、被引用列)

SELECT 
  kcu.CONSTRAINT_NAME, 
  kcu.TABLE_NAME, 
  kcu.COLUMN_NAME, 
  kcu.POSITION_IN_UNIQUE_CONSTRAINT,
  kcu.REFERENCED_TABLE_NAME, 
  kcu.REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
  ON tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA 
  AND tc.TABLE_NAME = kcu.TABLE_NAME 
  AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE kcu.TABLE_SCHEMA = 'sakila' 
  AND kcu.REFERENCED_TABLE_NAME = 'customer' 
  AND tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
ORDER BY kcu.CONSTRAINT_NAME, kcu.POSITION_IN_UNIQUE_CONSTRAINT;

7. 存儲過程/函數查詢

獲取所有存儲過程名稱

SELECT ROUTINE_NAME, CREATED 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_SCHEMA = 'sakila' AND ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME;

獲取存儲過程定義體

SELECT ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_SCHEMA = 'sakila' AND ROUTINE_NAME = 'film_in_stock';

獲取存儲過程參數 (MySQL 5.7+ 支援 PARAMETERS 表)

SELECT PARAMETER_NAME, PARAMETER_MODE, DATA_TYPE, DTD_IDENTIFIER
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'sakila' AND SPECIFIC_NAME = 'film_in_stock'
ORDER BY ORDINAL_POSITION;

8. 觸發器查詢

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING, ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = 'sakila'
ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION;

9. 統一對象查詢 (表/視圖/過程/函數/觸發器)

SELECT TABLE_NAME AS OBJECT_NAME, 'TABLE' AS OBJECT_TYPE, CREATE_TIME 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='sakila' AND TABLE_TYPE='BASE TABLE'
UNION ALL
SELECT TABLE_NAME, 'VIEW', CREATE_TIME 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='sakila' AND TABLE_TYPE='VIEW'
UNION ALL
SELECT ROUTINE_NAME, ROUTINE_TYPE, CREATED 
FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='sakila'
UNION ALL
SELECT TRIGGER_NAME, 'TRIGGER', CREATED 
FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='sakila'
ORDER BY OBJECT_TYPE, OBJECT_NAME;

自動化應用場景

文檔生成： 定時任務查詢上述元數據 → 渲染 Markdown/HTML → 發布至 Confluence/GitBook。
代碼生成： 讀取列定義 → 生成 JPA Entity / MyBatis Mapper / GraphQL Schema / TypeScript Interface。
Schema 比對： 對比 Dev/Test/Prod 環境 information_schema 導出結果 → 差異報警。
異構遷移驗證： MySQL → PostgreSQL 遷移後，對比雙方元數據確保列類型、約束、索引一致。

總結

information_schema 是 MySQL 元數據的「唯一真相來源」。掌握 TABLES、COLUMNS、STATISTICS、KEY_COLUMN_USAGE、TABLE_CONSTRAINTS、VIEWS、ROUTINES、TRIGGERS、PARAMETERS 九大核心表的聯查模式，即可構建任意自動化 Schema 管理工具鏈。本文模板經 sakila 庫實測，可直接適配生產環境任意庫。