MySQL ALTER TABLE 修改表結構整理：語法總覽與 11 大高頻實戰範例

完整語法圖

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

11 大高頻實戰範例

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;

Online DDL 與大表變更最佳實踐

• 5.6+ 支援 ALGORITHM=INPLACE, LOCK=NONE 多數操作
• 大表（>100萬行）建議：
  – 低峰期執行
  – 使用 pt-online-schema-change / gh-ost 雙寫零停機
  – 監控 SHOW PROCESSLIST 確認無長鎖
• CHANGE 需完整重定義欄位，MODIFY 僅調整型別/屬性
• 外鍵操作需參考表存在且型別一致

版本演進亮點

熟練 ALTER TABLE 各子句，配合線上變更工具，安全演進資料庫結構零停機。