表結構變更是資料庫生命週期中的高頻操作。本文系統整理 CREATE TABLE、ALTER TABLE（增刪改欄位、索引、主鍵、外鍵）、SHOW CREATE TABLE、SHOW INDEX 的完整語法與最佳實踐，支援 MySQL 5.7/8.0。

一、建表語法全解

CREATE TABLE `table_name` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
    `username` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用戶名',
    `email` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '郵箱',
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '狀態：1正常 0禁用',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`),
    KEY `idx_status_created` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用戶表';

關鍵選項說明

選項	說明	建議
ENGINE=InnoDB	存儲引擎	**預設 InnoDB**，支援事務、行鎖、外鍵、崩潰恢復
CHARSET=utf8mb4	字符集	支援完整 Unicode（含 Emoji），**禁用 utf8/latin1/gbk**
COLLATE=utf8mb4_unicode_ci	校對規則	Unicode 標準排序；general_ci 速度略快但排序不準
AUTO_INCREMENT	自增起始值	主鍵通常為 BIGINT UNSIGNED，避免耗盡
COMMENT	表/欄位註釋	**強制寫註釋**，元數據治理基礎
NOT NULL DEFAULT ''	非空+預設值	避免 NULL 語義模糊，索引效能更好
ON UPDATE CURRENT_TIMESTAMP	自動更新時間戳	8.0+ 支援多欄位，5.7 僅支援一個 TIMESTAMP/DATETIME

二、ALTER TABLE 修改表結構

1. 增加欄位

-- 末尾新增
ALTER TABLE tbl ADD COLUMN col_name VARCHAR(50) NOT NULL DEFAULT '' COMMENT '備註';

-- 指定位置新增
ALTER TABLE tbl ADD COLUMN col_name VARCHAR(50) NOT NULL DEFAULT '' COMMENT '備註' AFTER existing_col;
ALTER TABLE tbl ADD COLUMN col_name VARCHAR(50) NOT NULL DEFAULT '' COMMENT '備註' FIRST;

2. 刪除欄位

ALTER TABLE tbl DROP COLUMN col_name;

3. 修改欄位屬性（類型、長度、預設值、註釋）

-- 僅修改屬性，不改名
ALTER TABLE tbl MODIFY COLUMN col_name VARCHAR(100) NOT NULL DEFAULT '' COMMENT '新註釋';

-- 修改名稱 + 屬性
ALTER TABLE tbl CHANGE COLUMN old_name new_name VARCHAR(100) NOT NULL DEFAULT '' COMMENT '新註釋';

4. 索引管理

-- 普通索引
ALTER TABLE tbl ADD INDEX idx_col (col);
ALTER TABLE tbl ADD KEY idx_col (col);  -- 同義

-- 唯一索引
ALTER TABLE tbl ADD UNIQUE INDEX uk_col (col);
ALTER TABLE tbl ADD UNIQUE KEY uk_col (col);

-- 主鍵
ALTER TABLE tbl ADD PRIMARY KEY (col);
-- 移除主鍵（需先刪除 AUTO_INCREMENT）
ALTER TABLE tbl DROP PRIMARY KEY;

-- 復合索引（最左前綴生效）
ALTER TABLE tbl ADD INDEX idx_a_b_c (a, b, c);

-- 刪除索引
ALTER TABLE tbl DROP INDEX idx_col;
ALTER TABLE tbl DROP KEY idx_col;  -- 同義

-- 全文索引（MyISAM/InnoDB 5.6+）
ALTER TABLE tbl ADD FULLTEXT INDEX ft_content (content);

5. 外鍵管理

-- 新增外鍵
ALTER TABLE child
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE RESTRICT ON UPDATE CASCADE;

-- 刪除外鍵
ALTER TABLE child DROP FOREIGN KEY fk_child_parent;

6. 修改表選項

-- 修改字符集（僅影響新增欄位）
ALTER TABLE tbl CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- **完整轉換現有數據**（大表慎用，建議 pt-online-schema-change）
ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改表註釋
ALTER TABLE tbl COMMENT = '新表註釋';

-- 修改自增值
ALTER TABLE tbl AUTO_INCREMENT = 10000;

三、查看表結構與索引

-- 完整建表語句
SHOW CREATE TABLE tbl\G

-- 索引詳情
SHOW INDEX FROM tbl\G
-- 或
SHOW KEYS FROM tbl\G

-- 表狀態（行數、大小、自增值、引擎等）
SHOW TABLE STATUS LIKE 'tbl'\G

四、大表結構變更線上操作策略（避免鎖表阻塞）

操作	MySQL 5.6	MySQL 5.7	MySQL 8.0	建議工具
加欄位（末尾）	拷貝表	INPLACE	Instant DDL（秒級）	原生 / pt-osc
加索引	拷貝表	INPLACE	INPLACE（並行構建）	原生 / pt-osc
改欄位類型	拷貝表	拷貝表	INPLACE（部分）	**pt-osc / gh-ost**
修改字符集	拷貝表	拷貝表	拷貝表	**pt-osc / gh-ost**
刪除欄位	拷貝表	拷貝表	INPLACE	pt-osc
調整 AUTO_INCREMENT	INPLACE	INPLACE	Instant	原生

生產環境大表（> 100萬行/> 10GB）結構變更，強制使用 pt-online-schema-change 或 gh-ost，實現零停機、可暫停、可回滾。

五、命名規範建議

• 表名：單數、小寫、下劃線分隔 user_order
• 欄位名：小寫、下劃線、名詞 created_at
• 主鍵：id / table_id
• 索引：idx_<欄位1>_<欄位2>、uk_<欄位>、fk_<關聯表>_<欄位>
• 外鍵：fk_<本表>_<被引用表>