萬盛學電腦網

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

MySQL ALTER TABLE 修改欄位類型、名稱與索引的完整實戰手冊

ALTER TABLE:線上變更表結構的藝術與科學

MySQL ALTER TABLE 是 DDL 操作的核心,涉及鎖表、拷貝、重建、元數據鎖等復雜機制。錯誤的變更可能導致業務長時間阻塞、主從延遲甚至數據不一致。本文系統梳理 修改欄位類型、重命名欄位、新增/刪除索引 的語法、實戰案例、版本差異(5.6/5.7/8.0)與線上安全變更最佳實踐。

一、核心語法速查表

-- 修改欄位類型/名稱(CHANGE 需指定舊名,MODIFY 僅改類型)
ALTER TABLE tbl_name CHANGE old_col_name new_col_name column_definition [FIRST|AFTER col];
ALTER TABLE tbl_name MODIFY col_name column_definition [FIRST|AFTER col];

-- 新增/刪除欄位
ALTER TABLE tbl_name ADD col_name column_definition [FIRST|AFTER col];
ALTER TABLE tbl_name DROP COLUMN col_name;

-- 索引操作
ALTER TABLE tbl_name ADD INDEX idx_name (col1, col2);
ALTER TABLE tbl_name ADD UNIQUE uniq_name (col1);
ALTER TABLE tbl_name ADD PRIMARY KEY (col1);
ALTER TABLE tbl_name DROP INDEX idx_name;

二、修改欄位名稱與類型:實戰演示

建表基準

CREATE TABLE Employee (
    id INT,
    first_name VARCHAR(15),
    last_name VARCHAR(15),
    start_date DATE,
    end_date DATE,
    salary FLOAT(8,2),
    city VARCHAR(10),
    description VARCHAR(15)
);

案例 1:重命名欄位 + 擴大長度

ALTER TABLE Employee 
CHANGE First_Name FirstName VARCHAR(20);
  • CHANGE 必須指定舊欄位名、新欄位名、完整定義。
  • 此操作在 5.6 為 COPY 算法(鎖表拷貝),5.7+ 支援 INPLACE(僅元數據修改,極快)。

案例 2:僅修改類型(不改名)

ALTER TABLE Employee 
MODIFY Last_Name VARCHAR(50);
  • 使用 MODIFY 更語義化,避免誤輸入舊名。
  • 類型收窄(如 VARCHAR(50) → VARCHAR(10))需確保現有數據不超長,否則報錯 Data too long。

案例 3:調整欄位順序

ALTER TABLE Employee 
MODIFY city VARCHAR(20) AFTER first_name;
-- 或
ALTER TABLE Employee 
CHANGE city city VARCHAR(20) FIRST;

僅影響元數據,不重排數據頁(InnoDB 8.0+ 瞬間完成)。

三、索引操作:從添加到刪除的完整生命週期

新增唯一複合索引

ALTER TABLE myTable 
ADD UNIQUE (OrderID, ModelID);
  • 線上風險:大表加索引會佔用臨時空間(約表大小 1.5 倍),IO 飆升。建議低峰期執行或用 pt-online-schema-change。
  • 外鍵關聯:被引用欄位加索引需先刪除外鍵,變更後重建。

刪除索引

ALTER TABLE myTable DROP INDEX idx_name;
-- 或刪除主鍵
ALTER TABLE myTable DROP PRIMARY KEY;

四、MySQL 版本演進與算法差異(關鍵知識點)

操作類型5.6 及之前5.78.0
修改 VARCHAR 長度(增大)COPY(鎖表)INPLACE(秒級)INPLACE + 瞬時元數據(毫秒級)
修改 VARCHAR 長度(減小)COPYCOPY(需檢查數據)COPY(需檢查數據)
CHANGE/MODIFY 類型轉換COPYCOPY大多 COPY,部分 INPLACE
ADD/DROP INDEXCOPYINPLACEINPLACE + 並行構建
RENAME COLUMN❌ 不支援❌✅ INPLACE 瞬時

查看算法:EXPLAIN ALTER TABLE ... 或 SHOW WARNINGS 後檢查 ALGORITHM 欄位。

五、線上安全變更:三大工具對比

工具原理優點缺點適用場景
pt-online-schema-change觸發器同步 + 影子表換名成熟穩定、支援過濾、可暫停需 Perl 環境、觸發器有微小開銷通用生產環境首選
gh-ostBinlog 同步 + 影子表(無觸發器)無觸發器開銷、可調節流控、支援切換驗證需啟用 Row 格式 Binlog、學習曲線稍陡高併發、極大表(TB 級)
MySQL 8.0 瞬時 DDL純元數據修改零鎖、零拷貝、原生支援僅限部分操作(擴大 VARCHAR、增減欄位預設值等)符合瞬時條件的變更

六、避坑指南:血淚教訓總結

  1. 外鍵阻礙:修改被引用欄位類型前,必須 DROP FOREIGN KEY,否則報錯 Error 1832。
  2. 字符集陷阱:ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 會重建全表並轉換數據;CHANGE col col VARCHAR(100) CHARSET utf8mb4 僅改欄位定義,不轉換現有數據。
  3. 主從延遲:主庫 DDL 耗時 10 分鐘,從庫單線程重放可能延遲 30 分鐘。用 pt-osc --max-lag=1000 自動限速。
  4. 隱式提交:DDL 會隱式提交當前事務,事務中執行 DDL 會導致前面操作不可回滾。
  5. 元數據鎖 (MDL) 阻塞:長事務未提交時執行 DDL,會阻塞後續所有該表的查詢。變更前確認無長事務:SELECT * FROM information_schema.INNODB_TRX WHERE trx_mysql_thread_id IN (SELECT id FROM information_schema.PROCESSLIST WHERE db='your_db' AND time > 60);

七、標準化變更流程(建議納入 SOP)

  1. 測試環境驗證 SQL 語法與數據兼容性
  2. 評估表大小、索引大小、預計耗時、臨時空間需求
  3. 選擇工具:INSTANT → gh-ost → pt-osc → 原生 ALGORITHM=INPLACE
  4. 低峰期執行,監控 Threads_running、Innodb_row_lock_waits、磁盤 IO
  5. 驗證主從同步無延遲,應用端冒煙測試
  6. 記錄變更日誌,更新數據字典

變更表結構如同「給飛行中的飛機換引擎」,敬畏 DDL、工具先行、預案在手,才能在線上環境游刃有餘。

copyright © 萬盛學電腦網 all rights reserved