您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 獲取表結構元數據：DESC、SHOW、INFORMATION_SCHEMA 全方位指南

編輯：MySQL 資料庫相關知識

開發、遷移、逆向工程、自動化代碼生成，都離不開精準的表結構元數據。本文系統梳理 MySQL 獲取「庫、表、列、索引、備註」的所有標準方式，並給出生產級查詢模板。

一、經典簡單命令 (交互式首選)

命令	等價命令	輸出重點	適用場景
`DESC tbl_name;`	`DESCRIBE tbl_name;`	Field, Type, Null, Key, Default, Extra	快速查看列定義
`SHOW COLUMNS FROM tbl_name;`	`SHOW FIELDS FROM tbl_name;`	同上，可加 `LIKE 'pattern'`	過濾特定列
`SHOW CREATE TABLE tbl_name;`	—	完整建表 DDL (含引擎、字符集、分區、外鍵)	遷移、備份、審計
`SHOW TABLE STATUS [LIKE 'pattern'];`	—	行數、大小、引擎、自增值、創建時間、校驗和	容量評估、碎片分析
`SHOW INDEX FROM tbl_name;`	`SHOW KEYS FROM tbl_name;`	索引名、唯一性、列序、基數、類型 (BTREE/HASH)	索引優化、重複索引檢測

二、 INFORMATION_SCHEMA：程序化、標準化、跨庫查詢的核心

所有上述 SHOW 命令本質都是查詢 INFORMATION_SCHEMA 系統視圖。直接查視圖可實現：

跨庫批量獲取
複雜過濾/連接/聚合
應用代碼/ETL 腳本嵌入
權限控制 (只需 SELECT 權限)

核心視圖速查

視圖	核心列	典型用途
`SCHEMATA`	CATALOG_NAME, SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME	列舉所有庫
`TABLES`	TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH	庫/表清單、大小統計
`COLUMNS`	TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMN_TYPE, COLUMN_KEY, EXTRA, COLUMN_COMMENT	完整列定義、備註、自增、主鍵標識
`STATISTICS`	INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, NON_UNIQUE, INDEX_TYPE, CARDINALITY	索引結構分析
`TABLE_CONSTRAINTS`	CONSTRAINT_NAME, CONSTRAINT_TYPE (PRIMARY KEY, FOREIGN KEY, UNIQUE)	約束關係梳理
`KEY_COLUMN_USAGE`	REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME	外鍵引用鏈路

三、高頻實用查詢模板 (可直接用於代碼生成器)

1. 獲取指定表的完整列信息 (含備註)

SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    COLUMN_TYPE, 
    IS_NULLABLE, 
    COLUMN_DEFAULT, 
    COLUMN_KEY, 
    EXTRA, 
    COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table'
ORDER BY ORDINAL_POSITION;

2. 獲取庫下所有表名及行數估算

SELECT TABLE_NAME, TABLE_ROWS, 
       ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_mb
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_ROWS DESC;

3. 查找無主鍵表 (隱患排查)

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
  ON t.TABLE_SCHEMA = c.TABLE_SCHEMA 
  AND t.TABLE_NAME = c.TABLE_NAME 
  AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE t.TABLE_SCHEMA = 'your_db' 
  AND t.TABLE_TYPE = 'BASE TABLE'
  AND c.CONSTRAINT_NAME IS NULL;

4. 批量生建表 DDL (遷移/審計)

SELECT CONCAT('SHOW CREATE TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA IN ('db1','db2') AND TABLE_TYPE = 'BASE TABLE';

四、索引維護常用片段

刪除重複/冗余索引

ALTER TABLE tb_webparamcounter DROP INDEX AK_PAS_Name;
ALTER TABLE tb_webparamcounter ADD UNIQUE AK_PAS_Name (PC_ID, PAC_Name);

外鍵依賴順序調整

-- 必須先刪外鍵再改索引
ALTER TABLE tb_webparamcounter DROP FOREIGN KEY fk_name;
ALTER TABLE tb_webparamcounter DROP INDEX AK_PAS_Name;
ALTER TABLE tb_webparamcounter ADD UNIQUE AK_PAS_Name (PC_ID, PAC_Name, PAC_Value);