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

MySQL 取得資料庫所有表名、欄位定義、備註的標準查詢 (information_schema)

編輯：MySQL 資料庫相關知識

MySQL information_schema 系統資料庫提供標準化的元資料視圖，**跨版本、跨工具、可程式化查詢**，是取得表結構、欄位型別、索引、備註的權威來源。本文提供最常用的查詢範本與實務技巧。

核心視圖對照

視圖	用途	關鍵欄位
`TABLES`	所有表/視圖基本資訊	`TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, CREATE_TIME, TABLE_COMMENT`
`COLUMNS`	欄位詳細定義	`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`
`STATISTICS`	索引統計	`INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, COLLATION, CARDINALITY, SUB_PART, PACKED, NULLABLE, INDEX_TYPE, COMMENT`
`TABLE_CONSTRAINTS`	約束 (PK, FK, UNIQUE)	`CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME`
`KEY_COLUMN_USAGE`	約束涉及欄位	`CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME`
`REFERENTIAL_CONSTRAINTS`	外鍵關係詳情	`CONSTRAINT_NAME, UNIQUE_CONSTRAINT_NAME, MATCH_OPTION, UPDATE_RULE, DELETE_RULE`

常用查詢範本

1. 列出指定資料庫所有表名

SELECT TABLE_NAME, TABLE_COMMENT, ENGINE, TABLE_ROWS, CREATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

2. 取得單一表完整欄位定義 (含備註、型別、預設值、是否 PK)

SELECT 
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    COLUMN_KEY,          -- PRI, UNI, MUL, '' 
    EXTRA,               -- auto_increment, on update CURRENT_TIMESTAMP
    COLUMN_COMMENT,
    CHARACTER_MAXIMUM_LENGTH,
    NUMERIC_PRECISION,
    NUMERIC_SCALE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'users'
ORDER BY ORDINAL_POSITION;

3. 取得表所有索引結構

SELECT 
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns,
    NON_UNIQUE,
    INDEX_TYPE,
    INDEX_COMMENT
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders'
GROUP BY INDEX_NAME, NON_UNIQUE, INDEX_TYPE, INDEX_COMMENT
ORDER BY INDEX_NAME;

4. 產生建表 DDL (反向工程)

SELECT CONCAT(
    'CREATE TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` (\n',
    GROUP_CONCAT(CONCAT('  `', COLUMN_NAME, '` ', COLUMN_TYPE,
        IF(IS_NULLABLE='NO', ' NOT NULL', ''),
        IF(COLUMN_DEFAULT IS NOT NULL AND EXTRA NOT LIKE '%auto_increment%', CONCAT(' DEFAULT ', QUOTE(COLUMN_DEFAULT)), ''),
        IF(EXTRA='auto_increment', ' AUTO_INCREMENT', ''),
        IF(COLUMN_COMMENT!='', CONCAT(' COMMENT ', QUOTE(COLUMN_COMMENT)), '')
    ) ORDER BY ORDINAL_POSITION SEPARATOR ',\n'),
    ',\n  PRIMARY KEY (`', 
    (SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) 
     FROM information_schema.STATISTICS 
     WHERE TABLE_SCHEMA=t.TABLE_SCHEMA AND TABLE_NAME=t.TABLE_NAME AND INDEX_NAME='PRIMARY'),
    '`)\n) ENGINE=', ENGINE, ' DEFAULT CHARSET=', TABLE_COLLATION, ';'
) AS ddl
FROM information_schema.TABLES t
WHERE TABLE_SCHEMA='mydb' AND TABLE_NAME='users';

5. 修改索引實戰：複合唯一鍵調整

-- 原索引 AK_PAS_Name(PAC_Name)
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 INDEX AK_PAS_Name;
ALTER TABLE tb_webparamcounter ADD UNIQUE AK_PAS_Name (PC_ID, PAC_Name, PAC_Value);

-- 注意：若欄位涉及 FOREIGN KEY，必須先 DROP FOREIGN KEY 再調整索引