ALTER TABLE 語法核心
ALTER [IGNORE] TABLE tbl_name action_list;
-- action_list 多個動作以逗號分隔，單條語句原子執行
常用 Action 速查表
| 操作 | 語法範例 | 說明 |
|---|
| 新增欄位 | ADD COLUMN col_name type [FIRST|AFTER col] | 指定位置可選 |
| 刪除欄位 | DROP COLUMN col_name | 資料永久遺失 |
| 修改欄位定義 (含型別、預設值、註釋) | MODIFY COLUMN col_name new_type [NOT NULL|NULL] [DEFAULT val] [COMMENT '...'] | 不改名用 MODIFY |
| 修改欄位名稱 + 定義 | CHANGE COLUMN old_name new_name new_type [NOT NULL|NULL] [DEFAULT val] | 改名必須用 CHANGE |
| 新增主鍵 | ADD PRIMARY KEY (col1, col2) | 隱含 NOT NULL |
| 刪除主鍵 | DROP PRIMARY KEY | 僅一個 PK |
| 新增唯一索引 | ADD UNIQUE INDEX idx_name (col1, col2) |
| 新增普通索引 | ADD INDEX idx_name (col1, col2) |
| 刪除索引 | DROP INDEX idx_name |
| 重命名表 | RENAME TO new_tbl_name | 原子操作，無鎖 (MDL 除外) |
| 修改表選項 | ENGINE=InnoDB, CHARSET=utf8mb4, COMMENT='...', AUTO_INCREMENT=1000 | TABLE_OPTIONS |
| 轉換字元集 (含資料轉碼) | CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci | 資料重寫，大表慎用 |
| 僅修改預設字元集 (不轉換現有資料) | DEFAULT CHARACTER SET utf8mb4 | 僅影響新欄位 |
實戰範例：從欄位重命名到索引重構
-- 1. 修改欄位名稱與型別
ALTER TABLE shili CHANGE COLUMN id x_id INT;
-- 2. 新增欄位並指定位置
ALTER TABLE shili ADD COLUMN email VARCHAR(100) AFTER user;
-- 3. 修改欄位預設值與註釋
ALTER TABLE shili MODIFY COLUMN status TINYINT NOT NULL DEFAULT 1 COMMENT '1=正常 0=禁用';
-- 4. 重構索引：刪舊建新 (單條語句原子化)
ALTER TABLE shili
DROP INDEX idx_vtype,
ADD INDEX idx_vtype_id (vtype, id),
ADD UNIQUE INDEX uk_email (email);
-- 5. 修改表引擎與字元集 (大表建議 pt-online-schema-change)
ALTER TABLE shili ENGINE=InnoDB, CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 6. 重命名表
ALTER TABLE shili RENAME TO shili_v2;
大表 DDL 避坑指南 (5.6+ Online DDL / 8.0 Instant DDL)
| 操作 | 5.6/5.7 Online DDL | 8.0 Instant DDL | 鎖模式 | 建議工具 |
|---|
| 新增/刪除索引 | 支援 (INPLACE) | 支援 (INSTANT, 8.0.12+) | 共享鎖 → 獨佔鎖 (極短) | 原生 / pt-osc |
| 修改欄位型別 | 支援 (INPLACE, 需重建表) | 支援 (INPLACE) | 獨佔鎖 (準備/提交階段) | pt-osc 強烈推薦 |
| 新增/刪除欄位 | 支援 (INPLACE, 重建表) | 支援 (INSTANT, 8.0.12+ 部分) | 獨佔鎖 | pt-osc |
| 修改 CHARSET (CONVERT TO) | 不支援 Online (COPY) | 不支援 Instant | 獨佔鎖全程 | pt-osc 必須 |
| RENAME TABLE | Instant | Instant | MDL 獨佔 (極短) | 原生 |
pt-online-schema-change (pt-osc) 標準化流程
# 1. 檢查前置條件
pt-table-checksum --databases=db --tables=shili h=master,u=root,p=pass
# 2. 執行變更 (--dry-run 先預演)
pt-online-schema-change \
--alter "MODIFY COLUMN status TINYINT NOT NULL DEFAULT 1 COMMENT '1=正常 0=禁用'" \
--execute \
--max-load Threads_running=100 \
--critical-load Threads_running=200 \
--chunk-time 0.5 \
--chunk-size 1000 \
--progress time,30 \
--recursion-method=hosts \
--no-check-alter \
--no-swap-tables \
--statistics \
D=db,t=shili
# 參數解析：
# --max-load/--critical-load: 根據從庫壓力自動限流
# --chunk-time: 每塊目標執行時間，自動調整 chunk-size
# --no-swap-tables: 使用 RENAME TABLE 切換 (預設)，若有觸發器/外鍵需去除
驗證變更結果
SHOW CREATE TABLE shili_v2\G
SHOW INDEX FROM shili_v2;
SELECT COUNT(*) FROM shili_v2; -- 確認行數未變
ALTER TABLE 是 DBA 日常高頻操作，大表變更必須走「審核 → 測試庫驗證 → pt-osc 線上執行 → 校驗 → 釋出」流程。8.0 Instant DDL 讓新增欄位/索引/重命名近乎零鎖，但型別變更、字元集轉換仍需重建表，pt-osc 依然是生產大表變更的黃金標準。
