核心語法結構

ALTER TABLE tbl_name
    [alter_specification, ...]

alter_specification:
    ADD [COLUMN] col_def [FIRST | AFTER col_name]
  | ADD [CONSTRAINT symbol] PRIMARY KEY (col_name, ...)
  | ADD [CONSTRAINT symbol] UNIQUE [INDEX] [index_name] (col_name, ...)
  | ADD [CONSTRAINT symbol] INDEX [index_name] (col_name, ...)
  | ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (col_name, ...) reference_def
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_def [FIRST | AFTER col_name]
  | MODIFY [COLUMN] col_def [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | RENAME [TO | AS] new_tbl_name
  | table_options

常用操作實例

新增欄位

ALTER TABLE users ADD COLUMN email VARCHAR(100) AFTER name;
ALTER TABLE users ADD COLUMN status TINYINT DEFAULT 1 FIRST;

修改欄位名稱與型別

-- CHANGE：可改名、改型別、改位置
ALTER TABLE users CHANGE email user_email VARCHAR(150) NOT NULL;

-- MODIFY：只改型別/屬性，不可改名
ALTER TABLE users MODIFY user_email VARCHAR(200) DEFAULT '';

刪除欄位

ALTER TABLE users DROP COLUMN status;

索引操作

-- 普通索引
ALTER TABLE users ADD INDEX idx_email (user_email);

-- 唯一索引
ALTER TABLE users ADD UNIQUE INDEX uk_phone (phone);

-- 主鍵
ALTER TABLE users ADD PRIMARY KEY (id);

-- 刪除索引
ALTER TABLE users DROP INDEX idx_email;

重命名表

ALTER TABLE users RENAME TO members;

線上 DDL（Online DDL）與鎖模式

MySQL 5.6+ 支援 ALGORITHM 與 LOCK 子句：

ALTER TABLE huge_table 
    ADD INDEX idx_created (created_at),
    ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM	說明
INPLACE	原地重建，不鎖表或短暫鎖，支援大多數操作
COPY	複製表再切換，全程鎖表，舊版相容
INSTANT	8.0+ 秒級完成（僅限加欄位、改預設值等元資料操作）

LOCK	說明
NONE	不阻塞讀寫（若演算法支援）
SHARED	允許讀、阻塞寫
EXCLUSIVE	獨佔鎖，阻塞讀寫
DEFAULT	由伺服器決定

實戰範例

mysql> ALTER TABLE topics CHANGE hotico hot_count INT(4);
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE topics ALTER hot_count SET DEFAULT 1;
Query OK, 0 rows affected (0.01 sec)

大表變更建議

1. 使用 pt-online-schema-change (Percona Toolkit) 或 gh-ost 零停機變更。
2. 避開業務高峰，預留足夠磁碟空間（臨時表約等同原表大小）。
3. 監控 ALTER TABLE 進度：SHOW PROCESSLIST 或 performance_schema.events_stages_current。