ALTER TABLE 是 MySQL DDL 核心命令,支援表重命名、欄位增刪改、索引管理、引擎變更、分區操作等。本文通過完整命令列實戰演示,涵蓋生產環境高頻變更場景,並說明鎖行為、線上變更工具與最佳實踐。
mysql> CREATE DATABASE demo CHARSET utf8mb4;
mysql> USE demo;
mysql> CREATE TABLE test (
id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name CHAR(20) NOT NULL
) ENGINE=InnoDB;
-- 末尾新增
ALTER TABLE test ADD gender CHAR(4);
-- 指定位置新增(AFTER/FIRST)
ALTER TABLE test ADD age INT(4) AFTER name;
ALTER TABLE test ADD created_at TIMESTAMP FIRST;
-- 修改類型、長度、預設值、註釋(CHANGE 需指定舊名,MODIFY 僅屬性)
ALTER TABLE test CHANGE gender sex ENUM('M','F') DEFAULT 'M' COMMENT '性別';
ALTER TABLE test MODIFY age TINYINT UNSIGNED NOT NULL DEFAULT 0;
-- 修改欄位名(CHANGE 必須重複類型定義)
ALTER TABLE test CHANGE name full_name VARCHAR(50) NOT NULL;
ALTER TABLE test DROP COLUMN age;
-- 或
ALTER TABLE test DROP age;
-- 方式一:RENAME TABLE(可跨庫、多表原子重命名)
RENAME TABLE test TO hello;
RENAME TABLE hello TO world;
-- 方式二:ALTER TABLE RENAME
ALTER TABLE world RENAME TO test;
-- 新增索引
ALTER TABLE test ADD INDEX idx_name (name);
ALTER TABLE test ADD UNIQUE uk_email (email);
ALTER TABLE test ADD PRIMARY KEY (id); -- 若無 PK
-- 刪除索引
ALTER TABLE test DROP INDEX idx_name;
ALTER TABLE test DROP PRIMARY KEY;
-- 新增
ALTER TABLE child ADD CONSTRAINT fk_parent
FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE;
-- 刪除
ALTER TABLE child DROP FOREIGN KEY fk_parent;
ALTER TABLE test ENGINE = InnoDB;
ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
| 操作 | 5.7 | 8.0 | 說明 |
|---|---|---|---|
| ADD COLUMN (末尾) | INPLACE | INSTANT | 8.0.12+ 秒級完成,無鎖、無拷貝 |
| ADD COLUMN (指定位置) | INPLACE | INPLACE | 需重建表 |
| DROP COLUMN | COPY | INPLACE | 8.0 改為 INPLACE |
| CHANGE/MODIFY COLUMN | COPY | INPLACE/INSTANT* | 類型兼容可 INSTANT |
| RENAME TABLE | INSTANT | INSTANT | 僅元資料修改 |
| ADD/DROP INDEX | INPLACE | INPLACE | 並行構建、線上可讀寫 |
| CHANGE ENGINE | COPY | INPLACE | 8.0 改進 |
* 具體條件參考官方文檔 Online DDL Operations 章節。
原生 ALTER 大表會鎖表、佔用磁碟、影響主從延遲。生產大表(>10GB/1000萬行)必須用線上變更工具:
pt-online-schema-change \
--alter "ADD COLUMN status TINYINT NOT NULL DEFAULT 0" \
D=db,t=large_table \
--execute \
--max-load "Threads_running=50" \
--critical-load "Threads_running=100" \
--chunk-time 0.5 \
--recursion-method=hosts
gh-ost \
--host=localhost --user=root --password=xxx \
--database=db --table=large_table \
--alter="ADD COLUMN status TINYINT NOT NULL DEFAULT 0" \
--max-load=Threads_running=50 \
--critical-load=Threads_running=100 \
--chunk-size=1000 \
--execute
原理:建影子表 → 異步同步資料 → Binlog 回放追趕 → 原子切換。無觸發器、無鎖風險、可暫停/限流。
ALTER TABLE 功能強大但大表原生執行風險極高。MySQL 8.0 Instant DDL 大幅擴展了無鎖操作範圍,但複雜變更仍需 gh-ost/pt-osc。建立 Schema 變更管理規範,所有變更走工單、評估、預演、執行、驗證閉環,是資料庫穩定性的基石。