ALTER TABLE 是修改現有表結構的核心命令,涵蓋列增刪改、索引管理、表選項變更、重命名等。本文系統整理常用操作與最佳實踐。
CREATE TABLE testalter_tbl (
i INT,
c CHAR(1)
);
ALTER TABLE testalter_tbl DROP COLUMN i;
⚠️ 若表僅剩一列,無法 DROP。
-- 默認添加到末尾
ALTER TABLE testalter_tbl ADD COLUMN i INT;
-- 指定位置:首列
ALTER TABLE testalter_tbl ADD COLUMN i INT FIRST;
-- 指定位置:在 c 之後
ALTER TABLE testalter_tbl ADD COLUMN i INT AFTER c;
-- MODIFY:修改類型/屬性,列名不變
ALTER TABLE testalter_tbl MODIFY COLUMN c CHAR(10);
-- CHANGE:可同時修改列名和定義
ALTER TABLE testalter_tbl CHANGE COLUMN i j BIGINT;
-- 修改類型但保持列名
ALTER TABLE testalter_tbl CHANGE COLUMN j j INT;
-- 設置 NOT NULL + 默認值
ALTER TABLE testalter_tbl MODIFY COLUMN j BIGINT NOT NULL DEFAULT 100;
-- 僅修改默認值
ALTER TABLE testalter_tbl ALTER COLUMN i SET DEFAULT 1000;
-- 刪除默認值
ALTER TABLE testalter_tbl ALTER COLUMN i DROP DEFAULT;
-- 普通索引
ALTER TABLE tbl_name ADD INDEX idx_name (col1, col2);
-- 唯一索引
ALTER TABLE tbl_name ADD UNIQUE INDEX uk_name (col1);
-- 主鍵
ALTER TABLE tbl_name ADD PRIMARY KEY (col1);
-- 全文索引 (MySQL 5.6+ InnoDB, 5.7+ 內置 ngram 中文分詞)
ALTER TABLE tbl_name ADD FULLTEXT INDEX ft_name (col1, col2);
-- 普通/唯一/全文索引:需指定索引名
ALTER TABLE tbl_name DROP INDEX idx_name;
-- 主鍵:無需索引名
ALTER TABLE tbl_name DROP PRIMARY KEY;
SHOW INDEX FROM tbl_name\G
-- 或
SHOW KEYS FROM tbl_name;
ALTER TABLE testalter_tbl ENGINE = InnoDB;
驗證:SHOW TABLE STATUS LIKE 'testalter_tbl'\G
ALTER TABLE testalter_tbl RENAME TO alter_tbl;
-- 僅修改表默認字符集(影響新列)
ALTER TABLE tbl_name CHARACTER SET = utf8mb4;
-- 轉換現有列數據(推薦)
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
| 版本 | ALGORITHM | 特點 |
|---|---|---|
| 5.5 及之前 | COPY | 全表複製,鎖表,不可讀寫 |
| 5.6+ | INPLACE | 大多數操作原地修改,允許併發 DML |
| 8.0+ | INSTANT | 秒級完成,僅修改元數據(加列、刪列、改默認值等) |
-- 強制指定算法
ALTER TABLE tbl_name ADD COLUMN c INT, ALGORITHM=INPLACE, LOCK=NONE;
-- LOCK 選項:NONE(允許讀寫)、SHARED(僅允許讀)、EXCLUSIVE(阻塞讀寫)、DEFAULT(由服務器決定)
ALGORITHM=INPLACE, LOCK=NONECONVERT TO 而非 CHARACTER SETANALYZE TABLE 更新統計信息