ALTER TABLE 核心語法框架

ALTER TABLE tbl_name [alter_specification, ...]
alter_specification:
    ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
  | ADD [CONSTRAINT symbol] PRIMARY KEY [index_type] (col_name, ...)
  | ADD [CONSTRAINT symbol] UNIQUE [INDEX|KEY] [index_name] (col_name, ...)
  | ADD [CONSTRAINT symbol] INDEX [index_name] (col_name, ...)
  | ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (col_name, ...) reference_definition
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]
  | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DROP FOREIGN KEY fk_symbol
  | RENAME [TO|AS] new_tbl_name
  | ...

字段操作全集

1. 增加字段

-- 末尾新增
ALTER TABLE student ADD name VARCHAR(20);

-- 指定位置新增
ALTER TABLE student ADD age INT NOT NULL DEFAULT 18 AFTER name;
ALTER TABLE student ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST;

2. 修改字段（類型/默認值/註釋）

-- 修改類型、默認值、註釋（字段名不變用 MODIFY）
ALTER TABLE student MODIFY name VARCHAR(50) NOT NULL DEFAULT 'liming' COMMENT '學生姓名';

-- 修改字段名（必須用 CHANGE，即使類型不變也要重複聲明類型）
ALTER TABLE student CHANGE name student_name VARCHAR(50) NOT NULL DEFAULT 'liming' COMMENT '學生姓名';

3. 刪除字段

ALTER TABLE student DROP COLUMN name;

索引操作全集

1. 普通索引

-- 單列
ALTER TABLE student ADD INDEX idx_name (name);
-- 組合索引（最左前綴原則）
ALTER TABLE student ADD INDEX idx_class_age (class_id, age);

2. 唯一索引

ALTER TABLE student ADD UNIQUE INDEX uk_card (card_number);
-- 或
ALTER TABLE student ADD CONSTRAINT uk_card UNIQUE (card_number);

3. 刪除索引

ALTER TABLE student DROP INDEX idx_name;
-- 主鍵刪除單獨語法
ALTER TABLE student DROP PRIMARY KEY;

4. 查看索引

SHOW INDEX FROM student;
-- 或
SHOW KEYS FROM student\G

主鍵操作

1. 增加主鍵

-- 非自增主鍵
ALTER TABLE student ADD PRIMARY KEY (id);

-- 自增主鍵（需先確保列為 NOT NULL）
ALTER TABLE student MODIFY id INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (id);

2. 刪除主鍵

-- 非自增主鍵
ALTER TABLE student DROP PRIMARY KEY;

-- 自增主鍵（必須先去掉 AUTO_INCREMENT）
ALTER TABLE student MODIFY id INT, DROP PRIMARY KEY;

表級操作

1. 重命名表

ALTER TABLE old_name RENAME TO new_name;
-- 或
RENAME TABLE old_name TO new_name;

2. 修改表選項

ALTER TABLE student ENGINE = InnoDB;
ALTER TABLE student CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
ALTER TABLE student COMMENT = '學生信息表';

約束操作（外鍵、檢查、默認）

1. 外鍵

-- 添加
ALTER TABLE grade ADD CONSTRAINT fk_grade_class FOREIGN KEY (class_id) REFERENCES class(id);
-- 刪除
ALTER TABLE grade DROP FOREIGN KEY fk_grade_class;

2. 檢查約束（MySQL 8.0.16+ 生效）

ALTER TABLE grade ADD CONSTRAINT chk_sex CHECK (sex IN ('男','女'));
ALTER TABLE grade DROP CONSTRAINT chk_sex;

3. 默認值

ALTER TABLE grade ALTER COLUMN bak SET DEFAULT '我是學生';
ALTER TABLE grade ALTER COLUMN bak DROP DEFAULT;

PHP 代碼中動態添加字段示例

<?php
$link = mysqli_connect($host, $user, $pass, $db);
if (!$link) die('Connect failed: ' . mysqli_connect_error());

$sql = "ALTER TABLE `article` ADD `keywords` VARCHAR(100) NOT NULL DEFAULT ''";
if (mysqli_query($link, $sql)) {
    echo "Add succeed";
} else {
    echo "Error: " . mysqli_error($link);
}
mysqli_close($link);
?>

大表 DDL 性能與安全最佳實踐

1. MySQL 8.0+ 瞬時 DDL：ADD COLUMN、DROP INDEX 等多操作支持 ALGORITHM=INSTANT，無鎖、無拷貝
2. pt-online-schema-change：5.7/大表必備，影子表+觸發器零停機變更
3. 業務低峰期執行：配合 LOCK=NONE 或 LOCK=SHARED 控制鎖級別
4. 分批遷移數據：類型變更涉及數據重寫時，分批 UPDATE 避免長事務
5. 備份先行：DDL 前務必備份或開啟 binlog 確保可回滾

總結

ALTER TABLE 是 MySQL 結構變更的瑞士軍刀。掌握 ADD/MODIFY/CHANGE/DROP 字段、**索引/主鍵/外鍵/約束** 管理、**RENAME/ENGINE/CHARSET** 表級操作，並結合 Instant DDL / pt-osc / 低峰執行 / 備份先行 生產級策略，可安全高效完成從開發測試到核心業務上線的各類結構變更。