專案迭代中頻繁需要調整欄位長度、新增/刪除欄位、修改型別。MySQL 提供 ALTER TABLE 系列語法支援線上 DDL(5.6+ Online DDL),本文整理標準語法與常見坑點。
1. 新增欄位
ALTER TABLE `table_name`
ADD COLUMN `new_col` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '備註'
AFTER `existing_col`; -- 可選:指定位置
FIRST 放最前,AFTER col 放指定欄位後。
- 大表新增
NOT NULL 欄位需 DEFAULT,否則需全表重建回填。
2. 刪除欄位
ALTER TABLE `table_name`
DROP COLUMN `old_col`;
- 同步刪除該欄位上的索引、外鍵引用(需先刪除 FK)。
- InnoDB 5.6+ 為
INPLACE 操作,僅修改元資料,極快;舊版或含外鍵則 COPY 重建表。
3. 修改欄位(型別/長度/屬性/重命名)
MODIFY:不改名,改型別/屬性
ALTER TABLE `table_name`
MODIFY COLUMN `col_name` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '新備註';
CHANGE:可同時改名
ALTER TABLE `table_name`
CHANGE COLUMN `old_name` `new_name` VARCHAR(200) NOT NULL DEFAULT '';
CHANGE 必須指定新名稱(即使不改名也要重寫一次)。
- 型別變更需確保現有資料可隱式轉換,否則報錯/截斷。
4. 修改欄位順序
ALTER TABLE `table_name`
MODIFY COLUMN `col_name` INT FIRST; -- 移至首位
ALTER TABLE `table_name`
MODIFY COLUMN `col_name` INT AFTER `other_col`; -- 移至指定欄後
5. 批次操作(單條 ALTER 多動作)
ALTER TABLE `table_name`
ADD COLUMN `a` INT,
DROP COLUMN `b`,
MODIFY COLUMN `c` VARCHAR(50),
CHANGE COLUMN `d` `e` DATETIME;
- 單次
ALTER 合併多動作,僅掃描/重建一次表,大幅降低鎖表時間。
Online DDL 關鍵參數 (MySQL 5.6+)
| 參數 | 值 | 含義 |
ALGORITHM | DEFAULT | 由伺服器選擇 INPLACE/INSTANT/COPY |
| INPLACE | 原地重建,允許並發 DML,需臨時排序檔 |
| INSTANT | 僅修改元資料 (8.0.12+,僅支援加欄位、改預設值等) |
| COPY | 建臨時表拷貝,全程鎖表 (舊版預設) |
LOCK | DEFAULT | 由伺服器決定 |
| NONE | 不鎖表,並發 DML(INPLACE/INSTANT 才支援) |
| SHARED | 共享鎖,允許讀、阻寫 |
| EXCLUSIVE | 獨佔鎖,阻讀寫 |
範例:強制 INPLACE + 無鎖
ALTER TABLE `big_table`
ADD COLUMN `new_col` INT,
ALGORITHM=INPLACE, LOCK=NONE;
常見坑點
| 操作 | 風險 | 對策 |
| 修改主鍵 | 重建聚簇索引 = 全表重建 | 避免變更 PK;極必要用 pt-online-schema-change |
| VARCHAR 縮小長度 | 資料截斷報錯 | 先 UPDATE ... SET col=LEFT(col, new_len) 再 ALTER |
| INT → BIGINT | 安全,但索引體積翻倍 | 評估索引大小影響 |
| 加 NOT NULL 無 DEFAULT | 全表重建回填 | 先加 DEFAULT,再 ALTER MODIFY NOT NULL |
| 外鍵欄位變更 | 需先 DROP FK,改完再 ADD FK | 腳本化操作,驗證資料完整性 |
輔助工具
- pt-online-schema-change (Percona Toolkit):觸發器 + 分塊拷貝,零鎖表變更大表結構。
- gh-ost (GitHub):無觸發器,基於 Binlog 同步,更安全。
- MySQL Shell 8.0+:
\sql ALTER TABLE ... 可視化預覽影響。
熟練掌握 ADD/DROP/MODIFY/CHANGE 語法與 ALGORITHM/LOCK 控制,配合線上變更工具,讓 Schema 演進不再是上線障礙。
copyright ©
萬盛學電腦網 all rights reserved