MySQL ALTER TABLE 完整操作指南：欄位增刪改、索引管理、表重命名與引擎轉換

ALTER TABLE 是結構變更的核心命令，涵蓋欄位、索引、約束、引擎、分區、重命名等所有 DDL 操作。本文系統整理語法變體、線上/離線特性、效能影響與生產環境避坑清單。

一、欄位操作

-- 新增欄位（預設加在最後）
ALTER TABLE t ADD COLUMN `nickname` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '暱稱';
-- 指定位置
ALTER TABLE t ADD COLUMN `status` TINYINT NOT NULL DEFAULT 1 AFTER `name`;
ALTER TABLE t ADD COLUMN `sort` INT NOT NULL DEFAULT 0 FIRST;

-- 修改欄位定義（型別、長度、預設值、註解、NOT NULL）
ALTER TABLE t MODIFY COLUMN `nickname` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '暱稱';
-- 同時修改名稱與定義
ALTER TABLE t CHANGE COLUMN `nickname` `alias` VARCHAR(100) NOT NULL DEFAULT '';

-- 刪除欄位
ALTER TABLE t DROP COLUMN `alias`;

-- 修改預設值
ALTER TABLE t ALTER COLUMN `status` SET DEFAULT 0;
ALTER TABLE t ALTER COLUMN `status` DROP DEFAULT;

二、索引與約束管理

-- 新增索引
ALTER TABLE t ADD INDEX idx_status (status);
ALTER TABLE t ADD UNIQUE INDEX uk_email (email);
ALTER TABLE t ADD PRIMARY KEY (id);  -- 欄位必須 NOT NULL
ALTER TABLE t ADD FULLTEXT INDEX ft_content (content);

-- 刪除索引
ALTER TABLE t DROP INDEX idx_status;
ALTER TABLE t DROP PRIMARY KEY;  -- 主鍵無名稱，直接 DROP PRIMARY KEY

-- 外鍵（InnoDB）
ALTER TABLE orders ADD CONSTRAINT fk_orders_cust
    FOREIGN KEY (cust_id) REFERENCES customers(id)
    ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE orders DROP FOREIGN KEY fk_orders_cust;

三、表層級操作

-- 重命名表
ALTER TABLE t RENAME TO t_new;
-- 或
RENAME TABLE t TO t_new, t2 TO t2_new;  -- 原子性多表重命名

-- 轉換儲存引擎
ALTER TABLE t ENGINE = InnoDB;
-- 轉換字符集與排序規則
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 僅修改表預設字符集（不轉換現有欄位）
ALTER TABLE t DEFAULT CHARACTER SET utf8mb4;

-- 表選項調整
ALTER TABLE t AUTO_INCREMENT = 10000;
ALTER TABLE t COMMENT = '訂單主表';
ALTER TABLE t ROW_FORMAT = DYNAMIC;  -- 壓縮/動態行格式

四、線上 DDL 特性（MySQL 5.6+）

可強制指定：ALTER TABLE t ADD INDEX idx_x (x), ALGORITHM=INPLACE, LOCK=NONE; 若不支援會報錯，避免意外長時間鎖表。

五、生產環境避坑清單

1. 大表（> 100 萬行）禁用 COPY 算法：必須用 pt-online-schema-change 或 gh-ost。
2. 高峰期禁執行：即使 INPLACE 也會佔用 CPU/IO/MDL，安排在低峰窗口。
3. 預先檢查： SHOW CREATE TABLE、SHOW INDEX、SELECT COUNT(*)、磁碟空間 ≥ 2× 表大小。
4. 備份驗證：變更前做快照或邏輯備份，確認可恢復。
5. 監控指標： Threads_running、Innodb_row_lock_waits、Table_locks_waited、主從延遲。
6. 回滾預案：準備逆向 ALTER 語句，驗證可在 5 分鐘內執行完成。

六、常見錯誤與解決

熟練掌握 ALTER TABLE 的各種變體與線上特性，是 DBA 與開發者安全演進資料庫結構的基本功。