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

MySQL 元數據查詢速查：獲取所有表名、表結構字段的 information_schema 實戰 SQL

編輯：MySQL 綜合教學

核心視圖：information_schema

MySQL 系統庫，存儲所有元數據 (庫、表、列、索引、約束、權限等)，僅讀，查詢不加鎖，不影響業務。

1. 獲取指定庫所有表名 + 行數

SELECT 
  TABLE_NAME,
  TABLE_ROWS,
  TABLE_SCHEMA
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db_name'
  AND TABLE_TYPE = 'BASE TABLE';  -- 排除視圖

2. 獲取指定表所有字段詳情

SELECT 
  COLUMN_NAME,
  DATA_TYPE,
  COLUMN_TYPE,
  IS_NULLABLE,
  COLUMN_KEY,      -- PRI/UNI/MUL
  COLUMN_DEFAULT,
  EXTRA,           -- auto_increment
  COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db_name'
  AND TABLE_NAME = 'your_table_name'
ORDER BY ORDINAL_POSITION;

3. 進階：單條 SQL 獲取庫下所有表結構 (生成文檔/遷移腳本)

SELECT 
  c.TABLE_NAME,
  c.COLUMN_NAME,
  c.DATA_TYPE,
  c.COLUMN_TYPE,
  c.IS_NULLABLE,
  c.COLUMN_KEY,
  c.COLUMN_DEFAULT,
  c.EXTRA,
  c.COLUMN_COMMENT,
  t.TABLE_COMMENT
FROM information_schema.COLUMNS c
JOIN information_schema.TABLES t 
  ON c.TABLE_SCHEMA = t.TABLE_SCHEMA 
  AND c.TABLE_NAME = t.TABLE_NAME
WHERE c.TABLE_SCHEMA = 'your_db_name'
  AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION;

4. 常用元數據視圖速查

視圖	用途	關鍵列
`SCHEMATA`	數據庫列表	SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME
`TABLES`	表/視圖信息	TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, CREATE_TIME
`COLUMNS`	列定義	COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, EXTRA
`STATISTICS`	索引信息	INDEX_NAME, COLUMN_NAME, NON_UNIQUE, SEQ_IN_INDEX, CARDINALITY
`TABLE_CONSTRAINTS`	約束 (PK/FK/UNIQUE)	CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
`KEY_COLUMN_USAGE`	外鍵列引用關係	CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
`VIEWS`	視圖定義	VIEW_DEFINITION, CHECK_OPTION
`ROUTINES`	存儲過程/函數	ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION
`TRIGGERS`	觸發器	TRIGGER_NAME, EVENT_MANIPULATION, ACTION_STATEMENT
`PROCESSLIST`	當前線程 (同 SHOW PROCESSLIST)	ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO

5. 實用場景示例

查找所有無主鍵的表

SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND TABLE_NAME NOT IN (
    SELECT TABLE_NAME FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
  );

查找大表 (行數 > 1000萬)

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_ROWS > 10000000
ORDER BY TABLE_ROWS DESC;

統計各庫表數量

SELECT TABLE_SCHEMA, COUNT(*) AS table_count
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GROUP BY TABLE_SCHEMA
ORDER BY table_count DESC;