萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL ALTER TABLE 完整操作指南:修改表結構、索引、字符集

MySQL ALTER TABLE 全方位操作手冊

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;

修改 NULL/DEFAULT 屬性

-- 設置 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;

DDL 性能與在線變更

MySQL 版本演進

版本ALGORITHM特點
5.5 及之前COPY全表複製,鎖表,不可讀寫
5.6+INPLACE大多數操作原地修改,允許併發 DML
8.0+INSTANT秒級完成,僅修改元數據(加列、刪列、改默認值等)

控制 DDL 算法

-- 強制指定算法
ALTER TABLE tbl_name ADD COLUMN c INT, ALGORITHM=INPLACE, LOCK=NONE;

-- LOCK 選項:NONE(允許讀寫)、SHARED(僅允許讀)、EXCLUSIVE(阻塞讀寫)、DEFAULT(由服務器決定)

大表變更工具推薦

  • pt-online-schema-change (Percona Toolkit):觸發器+影子表,零停機變更
  • gh-ost (GitHub):無觸發器,基於 Binlog 同步,更安全
  • OSC (Online Schema Change):各大雲廠商托管服務內置

最佳實踐清單

  1. 變更前完整備份(邏輯/物理)
  2. 低峰期執行,預留足夠磁盤空間(臨時表需 1-2 倍表大小)
  3. 優先使用 ALGORITHM=INPLACE, LOCK=NONE
  4. 大表(>10GB)必須使用 pt-osc/gh-ost
  5. 修改字符集用 CONVERT TO 而非 CHARACTER SET
  6. 外鍵關聯表變更需注意順序:先刪除外鍵 → 變更 → 重建外鍵
  7. 變更後執行 ANALYZE TABLE 更新統計信息
copyright © 萬盛學電腦網 all rights reserved