MySQL ALTER TABLE 修改表結構完整指南

ALTER TABLE 是 MySQL 中修改現有表結構的核心命令，涵蓋表屬性、欄位屬性、索引與約束的增刪改。本文系統整理各類修改操作的語法、範例與注意事項。

語法總覽

ALTER [IGNORE] TABLE <table_name> <alter_specification> [, <alter_specification>]...

支援一次語句組合多個修改操作，減少鎖表次數與資料複製開銷。

1. 表屬性修改

1.1 重命名表

-- 方式一：ALTER TABLE RENAME
ALTER TABLE `student` RENAME TO `s`;

-- 方式二：RENAME TABLE (可跨資料庫、多表原子重命名)
RENAME TABLE `s` TO `student`;

建議：使用 RENAME TABLE，支援多表原子操作，避免中間狀態可見性問題。

1.2 表排序優化

ALTER TABLE `student` ORDER BY `stu_id` DESC;

物理重排資料頁，使主鍵順序與查詢順序一致，提升範圍掃描效能。注意：僅對 MyISAM/InnoDB 主鍵聚簇索引有效，大表操作會鎖表長時間，建議低峰期執行。

2. 欄位屬性修改

2.1 新增欄位

ALTER TABLE `student` 
ADD `sex` CHAR(1) AFTER `stu_name`;

• 預設追加至末尾，可用 FIRST 或 AFTER col_name 指定位置。
• 新欄位若無 NOT NULL 則預設填 NULL；若有 NOT NULL 且無 DEFAULT，MySQL 會依類型填充預設值（數值 0、字串空、日期 '0000-00-00'）。

2.2 刪除欄位

ALTER TABLE `student` DROP `sex`;

同步移除該欄位相關索引與約束，不可恢復，謹慎操作。

2.3 修改欄位定義

範例：將 stu_tel INT 改為 tel CHAR(3) 並移至 stu_score 之後：

ALTER TABLE `student` CHANGE `stu_tel` `tel` CHAR(3) AFTER `stu_score`;

再改回 INT：

ALTER TABLE `student` MODIFY `tel` INT(3);

3. 完整性約束修改

3.1 索引與約束操作語法

<integrity_constraint_change> ::= 
    ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part, ...)
  | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (key_part, ...)
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_list) REFERENCES tbl_name (col_list) [ON DELETE reference_option] [ON UPDATE reference_option]
  | ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  | DROP CONSTRAINT constraint_name
  | DROP INDEX index_name
  | ALTER INDEX index_name {VISIBLE|INVISIBLE}

3.2 常用操作範例

-- 刪除主鍵
ALTER TABLE `student` DROP PRIMARY KEY;

-- 新增主鍵
ALTER TABLE `student` ADD PRIMARY KEY (`stu_id`);

-- 新增唯一索引
ALTER TABLE `student` ADD UNIQUE KEY `uk_stu_name` (`stu_name`);

-- 新增外鍵 (假設 class 表存在)
ALTER TABLE `student` 
ADD CONSTRAINT `fk_student_class` 
FOREIGN KEY (`class_id`) REFERENCES `class`(`class_id`) 
ON UPDATE CASCADE ON DELETE RESTRICT;

-- 新增檢查約束 (MySQL 8.0.16+)
ALTER TABLE `student` ADD CONSTRAINT `chk_score` CHECK (`stu_score` BETWEEN 0 AND 100);

-- 刪除外鍵 (需先查詢 CONSTRAINT_NAME)
ALTER TABLE `student` DROP FOREIGN KEY `fk_student_class`;

-- 刪除索引
ALTER TABLE `student` DROP INDEX `uk_stu_name`;

4. 進階技巧與最佳實踐

4.1 合併多個 ALTER 操作

錯誤做法（兩次鎖表、兩次複製）：

ALTER TABLE `t` ADD COLUMN `b` VARCHAR(10);
ALTER TABLE `t` ADD INDEX `idx_aa` (`aa`);

正確做法（單次鎖表、單次複製）：

ALTER TABLE `t` 
ADD COLUMN `b` VARCHAR(10),
ADD INDEX `idx_aa` (`aa`);

4.2 大表修改策略

1. 線上 DDL (Online DDL)：MySQL 5.6+ InnoDB 支援大部分操作非阻塞 DML，但仍有鎖表瞬間（準備/提交階段）。
2. pt-online-schema-change / gh-ost：觸發器或二進位日誌模擬影子表同步，實現真正無鎖結構變更，大表必備。
3. 分批遷移：新建表 → 雙寫 → 歷史資料分批同步 → 切換流量 → 下舊表。

4.3 字符集與排序規則修改

-- 僅修改表預設字符集 (不影響現有欄位)
ALTER TABLE `t` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

-- 連同現有欄位一起轉換 (會重建表)
ALTER TABLE `t` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

4.4 禁用/啟用索引 (大量導入優化)

ALTER TABLE `t` DISABLE KEYS;  -- 僅 MyISAM 有效，InnoDB 忽略
-- 大量 INSERT/LOAD DATA
ALTER TABLE `t` ENABLE KEYS;   -- 重建索引

5. 常見錯誤與解決

掌握 ALTER TABLE 的完整語法與實戰技巧，能讓資料庫結構演進更安全、高效，是 DBA 與後端開發必備核心技能。