萬盛學電腦網

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

MySQL ALTER TABLE 實戰:增刪欄位、修改表名、變更欄位屬性完整語法指南

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;

核心操作演示

1. 新增欄位

-- 末尾新增
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;

2. 修改欄位屬性

-- 修改類型、長度、預設值、註釋(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;

3. 刪除欄位

ALTER TABLE test DROP COLUMN age;
-- 或
ALTER TABLE test DROP age;

4. 重命名表

-- 方式一:RENAME TABLE(可跨庫、多表原子重命名)
RENAME TABLE test TO hello;
RENAME TABLE hello TO world;

-- 方式二:ALTER TABLE RENAME
ALTER TABLE world RENAME TO test;

5. 索引管理

-- 新增索引
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;

6. 外鍵管理

-- 新增
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;

7. 引擎/字符集變更

ALTER TABLE test ENGINE = InnoDB;
ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

MySQL 8.0 線上 DDL(Instant DDL)支援矩陣

操作5.78.0說明
ADD COLUMN (末尾)INPLACEINSTANT8.0.12+ 秒級完成,無鎖、無拷貝
ADD COLUMN (指定位置)INPLACEINPLACE需重建表
DROP COLUMNCOPYINPLACE8.0 改為 INPLACE
CHANGE/MODIFY COLUMNCOPYINPLACE/INSTANT*類型兼容可 INSTANT
RENAME TABLEINSTANTINSTANT僅元資料修改
ADD/DROP INDEXINPLACEINPLACE並行構建、線上可讀寫
CHANGE ENGINECOPYINPLACE8.0 改進

* 具體條件參考官方文檔 Online DDL Operations 章節。

大表變更生產級方案:pt-online-schema-change / gh-ost

原生 ALTER 大表會鎖表、佔用磁碟、影響主從延遲。生產大表(>10GB/1000萬行)必須用線上變更工具:

pt-online-schema-change (Percona Toolkit)

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 (GitHub Open Source)

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 回放追趕 → 原子切換。無觸發器、無鎖風險、可暫停/限流。

變更前檢查清單

  1. ☐ 確認變更必要性、向後兼容性
  2. ☐ 評估表大小、行數、索引大小、主從延遲基線
  3. ☐ 選擇工具:小表原生 ALTER、大表 gh-ost/pt-osc
  4. ☐ 低峰期執行、預留 2-3 倍表大小磁碟空間
  5. ☐ 準備回滾方案(備份、RENAME 回滾)
  6. ☐ 變更後驗證:結構、數據、索引、主從一致性、應用功能

總結

ALTER TABLE 功能強大但大表原生執行風險極高。MySQL 8.0 Instant DDL 大幅擴展了無鎖操作範圍,但複雜變更仍需 gh-ost/pt-osc。建立 Schema 變更管理規範,所有變更走工單、評估、預演、執行、驗證閉環,是資料庫穩定性的基石。

copyright © 萬盛學電腦網 all rights reserved