萬盛學電腦網

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

MySQL ALTER TABLE 實戰大全:增刪欄位、重命名表、修改字段屬性完全指南

ALTER TABLE 核心語法速查

操作語法是否重建表鎖表級別
添加欄位ADD COLUMN col_name type [FIRST|AFTER col]通常 INPLACE元數據鎖
刪除欄位DROP COLUMN col_nameREBUILD排他鎖
修改欄位類型/屬性MODIFY COLUMN col_name new_typeREBUILD排他鎖
重命名欄位CHANGE old_name new_name type通常 INPLACE元數據鎖
重命名表RENAME TO new_table_nameINPLACE元數據鎖
添加索引ADD INDEX idx_name (col)INPLACE (5.6+)共享鎖
修改表選項ENGINE=InnoDB, CHARSET=utf8mb4REBUILD排他鎖

實戰演示環境準備

CREATE DATABASE alter_demo CHARSET utf8mb4;
USE alter_demo;

CREATE TABLE users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(30) NOT NULL COMMENT '用戶名',
  email VARCHAR(100) NOT NULL COMMENT '郵箱'
) ENGINE=InnoDB COMMENT='用戶表';

INSERT INTO users (username, email) VALUES 
('alice', 'alice@example.com'),
('bob', 'bob@example.com');

場景一:添加欄位

在末尾添加

ALTER TABLE users ADD COLUMN phone VARCHAR(20) COMMENT '手機號';
-- 結果:phone 成為最後一列

指定位置添加(FIRST / AFTER)

-- 作為第一列
ALTER TABLE users ADD COLUMN uuid CHAR(36) FIRST COMMENT '唯一標識';

-- 在 email 之後添加
ALTER TABLE users ADD COLUMN status TINYINT DEFAULT 1 AFTER email COMMENT '狀態:1啟用 0禁用';

批量添加多欄位

ALTER TABLE users 
  ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間';

場景二:刪除欄位

-- 刪除單欄位
ALTER TABLE users DROP COLUMN phone;

-- 刪除多欄位(單條語句,僅重建一次表)
ALTER TABLE users DROP COLUMN uuid, DROP COLUMN status;

⚠️ 注意:刪除欄位會導致表重建,大表操作會長時間鎖表,建議使用 pt-online-schema-change。

場景三:修改欄位屬性

修改數據類型/長度

-- 擴大長度(安全)
ALTER TABLE users MODIFY COLUMN username VARCHAR(50) NOT NULL;

-- 縮小長度(風險:數據截斷)
ALTER TABLE users MODIFY COLUMN username VARCHAR(20) NOT NULL;
-- 執行前必須確認:SELECT MAX(CHAR_LENGTH(username)) FROM users;

修改默認值/註釋/非空約束

-- 修改默認值
ALTER TABLE users MODIFY COLUMN status TINYINT DEFAULT 1;

-- 修改註釋
ALTER TABLE users MODIFY COLUMN email VARCHAR(100) NOT NULL COMMENT '登錄郵箱';

-- 移除非空約束
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NULL COMMENT '可選手機號';

-- 添加非空約束(需確保無 NULL 值)
ALTER TABLE users MODIFY COLUMN email VARCHAR(100) NOT NULL;

重命名欄位

-- CHANGE 需指定完整類型定義
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(50) NOT NULL COMMENT '用戶名';

場景四:重命名表

方法一:RENAME TABLE(推薦,可跨庫)

RENAME TABLE users TO app_users;
-- 或跨庫
RENAME TABLE db1.users TO db2.app_users;

方法二:ALTER TABLE RENAME

ALTER TABLE users RENAME TO app_users;

場景五:修改表級選項

-- 修改存儲引擎
ALTER TABLE users ENGINE = InnoDB;

-- 修改字符集(會轉換現有數據)
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改表註釋
ALTER TABLE users COMMENT = '核心業務用戶表';

大表 DDL 最佳實踐

大表(>100萬行或 >1GB)DDL 原則:
  1. 使用 pt-online-schema-change 或 gh-ost 實現無鎖變更
  2. 低峰期執行,預留足夠磁盤空間(臨時表需 1.5 倍空間)
  3. 監控 SHOW PROCESSLIST 和 INNODB_METRICS 進度
  4. 避免在主庫高峰期執行,防止主從延遲

MySQL 8.0 即時 DDL 特性

MySQL 8.0.12+ 支持 Instant DDL,以下操作無需重建表、幾乎無鎖:

  • 添加/刪除虛擬欄位
  • 修改欄位默認值
  • 修改欄位註釋
  • 添加/刪除二級索引(部分場景)
  • 修改 ENUM/SET 列表(僅追加值)
-- 檢查是否支持 Instant
SHOW VARIABLES LIKE 'innodb_default_row_format';
-- DYNAMIC 格式支持更多 Instant 操作
copyright © 萬盛學電腦網 all rights reserved