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

語法總覽

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

alter_specification:
    RENAME TO new_tbl_name
  | RENAME [COLUMN] old_col_name TO new_col_name
  | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
  | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFEND col_name]
  | ADD PRIMARY KEY (col_name, ...)
  | ADD UNIQUE [INDEX|KEY] [index_name] (col_name, ...)
  | ADD INDEX [index_name] (col_name, ...)
  | ADD FULLTEXT [INDEX|KEY] [index_name] (col_name, ...)
  | ADD FOREIGN KEY [index_name] (col_name, ...) REFERENCES tbl_name (col_name, ...)
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DROP FOREIGN KEY fk_symbol

1. 表層級操作

重命名資料表

ALTER TABLE student RENAME TO s;
-- 或使用獨立指令（可一次改多表）
RENAME TABLE s TO student, old2 TO new2;

調整資料排序（物理重組）

ALTER TABLE student ORDER BY stu_id DESC;

注意：InnoDB 會按主鍵聚簇儲存，ORDER BY 僅對 MyISAM/MEMORY 有實質效果；InnoDB 8.0+ 已移除此選項。

2. 欄位層級操作

新增欄位

ALTER TABLE student ADD sex CHAR(1) AFTER stu_name;
-- 預設值處理：NOT NULL 欄位會依型別填預設值 (數值 0、字串 ''、日期 '0000-00-00')

刪除欄位

ALTER TABLE student DROP sex;

修改欄位定義

-- 將 stu_tel 改為 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. 完整性約束操作

-- 主鍵
ALTER TABLE student DROP PRIMARY KEY;
ALTER TABLE student ADD PRIMARY KEY (stu_id);

-- 唯一索引
ALTER TABLE student ADD UNIQUE uk_email (email);

-- 外鍵
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE ON UPDATE CASCADE;

-- 刪除外鍵（需知道 constraint 名稱）
ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;

4. 線上 DDL (Online DDL) 提示

• MySQL 5.6+ 大部分 ALTER TABLE 支援 ALGORITHM=INPLACE，不鎖表、不拷貝全表。
• 強制拷貝操作：CHANGE COLUMN 改型別、ADD PRIMARY KEY (5.6)、CONVERT TO CHARACTER SET。
• 生產環境建議加上 ALGORITHM=INPLACE, LOCK=NONE 並在低峰期執行。

5. 常見錯誤與對策