MySQL 查看表結構與建表語句命令大全：DESC、SHOW CREATE TABLE、INFORMATION_SCHEMA

MySQL 查看表結構與建表語句命令大全：DESC、SHOW CREATE TABLE、INFORMATION_SCHEMA

準確獲取表結構信息是開發、運維、遷移、文檔生成的基礎操作。本文系統整理三種核心方法、輸出差異及適用場景。

一、DESC / DESCRIBE - 快速查看列定義

mysql> DESC authors;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| email | varchar(100) | NO   | UNI | NULL    |                |
| name  | varchar(100) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

-- 等價命令
DESCRIBE authors;
SHOW COLUMNS FROM authors;

輸出字段含義：

• Field：列名
• Type：數據類型
• Null：是否允許 NULL (YES/NO)
• Key：索引類型 (PRI=主鍵, UNI=唯一, MUL=非唯一索引)
• Default：默認值
• Extra：額外屬性 (auto_increment, on update CURRENT_TIMESTAMP 等)

二、SHOW CREATE TABLE - 獲取完整建表語句

mysql> SHOW CREATE TABLE authors\G;
*************************** 1. row ***************************
       Table: authors
Create Table: CREATE TABLE `authors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

核心優勢：

• 包含完整 DDL：引擎、字符集、自增值、索引定義、外鍵約束
• 可直接用於環境復刻、版本控制、遷移腳本生成
• \G 垂直顯示適合寬表閱讀

三、INFORMATION_SCHEMA.COLUMNS - 程序化查詢元數據

mysql> USE information_schema;
Database changed

mysql> SELECT 
    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,
    COLUMN_COMMENT
FROM COLUMNS 
WHERE TABLE_SCHEMA='your_db' AND TABLE_NAME='authors';

+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+-------------------+---------------+--------------+------------+----------------+----------------+
| 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        | COLUMN_COMMENT |
+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+-------------------+---------------+--------------+------------+----------------+----------------+
| your_db      | authors    | id          | 1                | NULL           | NO          | int       | NULL                     | 10                | 0             | int(11)      | PRI        | auto_increment |                |
| your_db      | authors    | email       | 2                | NULL           | NO          | varchar   | 100                      | NULL              | NULL          | varchar(100) | UNI        |              |                |
| your_db      | authors    | name        | 3                | NULL           | NO          | varchar   | 100                      | NULL              | NULL          | varchar(100) |            |              |                |
+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+-------------------+---------------+--------------+------------+----------------+----------------+

適用場景：

• 自動化工具開發（代碼生成、文檔生成、Schema Diff）
• 跨庫/跨表批量結構對比
• 動態構建查詢條件、驗證邏輯
• 元數據治理平台數據源

四、三種方法對比選擇指南

五、進階用法與組合技

1. 批量獲取多表結構

-- 生成多表建表語句
SELECT CONCAT('SHOW CREATE TABLE `', TABLE_NAME, '`;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='db_name' AND TABLE_TYPE='BASE TABLE';

-- 導出為文件
mysql -uroot -p -e "SELECT CONCAT('SHOW CREATE TABLE \`', TABLE_NAME, '\`;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db'" -sN | mysql -uroot -p db > schema.sql

2. 結構對比腳本思路

# 獲取兩庫表結構差異
# 1. 導出結構
mysqldump -d db1 > db1_schema.sql
mysqldump -d db2 > db2_schema.sql
# 2. 對比
diff -u db1_schema.sql db2_schema.sql
# 或使用專業工具：schemadiff, pt-table-sync, Flyway/Liquibase

3. 獲取表大小與統計信息

SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH/1024/1024, 2) AS index_mb,
    ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024, 2) AS total_mb,
    ENGINE,
    TABLE_COLLATION,
    CREATE_TIME,
    UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='db_name'
ORDER BY DATA_LENGTH DESC;

六、常見問題與注意事項

1. 視圖無 DESC 信息：DESC view_name 顯示視圖列定義，但無索引/引擎信息
2. 分區表 SHOW CREATE TABLE 會包含 PARTITION 子句
3. INFORMATION_SCHEMA 查詢大量表可能慢：受 innodb_stats_on_metadata 影響，建議關閉或緩存
4. 8.0 隱藏列：INFORMATION_SCHEMA.COLUMNS 新增 IS_VISIBLE 字段標識隱藏列