MySQL ALTER TABLE 修改表結構完整語法與實戰範例

語法總覽

ALTER [IGNORE] TABLE tbl_name
  alter_spec [, alter_spec ...]

alter_spec:
  ADD [COLUMN] col_def [FIRST | AFTER col_name]
  | ADD INDEX [idx_name] (col_list)
  | ADD PRIMARY KEY (col_list)
  | ADD UNIQUE [idx_name] (col_list)
  | ALTER [COLUMN] col_name {SET DEFAULT val | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name col_def
  | MODIFY [COLUMN] col_def
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX idx_name
  | RENAME [AS] new_tbl_name
  | table_options

高頻實戰範例

1. 新增欄位

ALTER TABLE employee ADD COLUMN account_number INT;

2. 新增索引

ALTER TABLE employee ADD INDEX idx_id (id);

3. 設定主鍵

ALTER TABLE employee ADD PRIMARY KEY (id);

4. 設定唯一索引

ALTER TABLE employee ADD UNIQUE uk_email (email);

5. 重命名欄位並變更型別

ALTER TABLE employee CHANGE id salary INT;

6. 刪除欄位

ALTER TABLE employee DROP COLUMN customer_id;

7. 移除主鍵

ALTER TABLE employee DROP PRIMARY KEY;

8. 移除索引（保留欄位）

ALTER TABLE employee DROP INDEX idx_customer_id;

9. 修改欄位型別/長度

ALTER TABLE employee MODIFY first_name VARCHAR(100);

10. 重命名表

ALTER TABLE employee RENAME TO customer;

11. 批次多操作（單次鎖表）

ALTER TABLE books
  ADD PRIMARY KEY (book_id),
  ADD CONSTRAINT fk_pub FOREIGN KEY (pub_id) REFERENCES publishers(pub_id),
  ADD COLUMN format ENUM('paperback','hardcover') NOT NULL AFTER book_name;

效能與鎖表注意事項

• MySQL 5.6+ Online DDL：大部分操作支援 ALGORITHM=INPLACE、LOCK=NONE，減少鎖表時間
• 大表變更建議：
  – 低峰期執行
  – 使用 pt-online-schema-change 或 gh-ost 雙寫工具零停機變更
  – 監控 SHOW PROCESSLIST 確認無長鎖
• CHANGE 需完整重定義欄位定義，MODIFY 僅調整型別/屬性
• 外鍵操作需參考表存在且型別一致

版本差異提醒

• 8.0+ 支援 INSTANT ADD COLUMN（僅元數據修改，極快）
• 8.0+ DROP COLUMN 也支援 INPLACE
• 5.7 請確認 innodb_file_per_table=ON 以利空間回收

熟練 ALTER TABLE 各子句，配合 Online DDL 與線上變更工具，即可安全演進資料庫結構。