萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 動態修改表結構:ADD、DROP、CHANGE/MODIFY COLUMN 完整語法

專案迭代中頻繁需要調整欄位長度、新增/刪除欄位、修改型別。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+)

範例:強制 INPLACE + 無鎖

ALTER TABLE `big_table`
ADD COLUMN `new_col` INT,
ALGORITHM=INPLACE, LOCK=NONE;

常見坑點

參數值含義
ALGORITHMDEFAULT由伺服器選擇 INPLACE/INSTANT/COPY
INPLACE原地重建,允許並發 DML,需臨時排序檔
INSTANT僅修改元資料 (8.0.12+,僅支援加欄位、改預設值等)
COPY建臨時表拷貝,全程鎖表 (舊版預設)
LOCKDEFAULT由伺服器決定
NONE不鎖表,並發 DML(INPLACE/INSTANT 才支援)
SHARED共享鎖,允許讀、阻寫
EXCLUSIVE獨佔鎖,阻讀寫

輔助工具

  • 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
操作風險對策
修改主鍵重建聚簇索引 = 全表重建避免變更 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腳本化操作,驗證資料完整性