MySQL DDL 操作大全：ALTER TABLE 實戰語法與最佳實務

ALTER TABLE 是修改現有表結構的核心命令。本文系統整理增/刪/改欄位、重命名表/欄位、修改資料型別、調整約束、索引維護等高頻操作，並對比 SQL Server / Oracle 語法差異。

基礎查看與重命名

-- 查看表結構
DESC tbl_name;
SHOW CREATE TABLE tbl_name;

-- 修改表名
ALTER TABLE t_book RENAME TO bbb;
-- 或
RENAME TABLE t_book TO bbb;

欄位增刪改

新增欄位

-- 末尾新增
ALTER TABLE tbl_name ADD COLUMN col_name VARCHAR(30);

-- 指定位置新增（FIRST / AFTER col）
ALTER TABLE tbl_name ADD COLUMN col_name INT FIRST;
ALTER TABLE tbl_name ADD COLUMN col_name INT AFTER existing_col;

刪除欄位

ALTER TABLE tbl_name DROP COLUMN col_name;

修改欄位名稱

-- MySQL：CHANGE 老名 新名 新型別（必須重複指定型別）
ALTER TABLE bbb CHANGE nnnnn hh INT;

-- SQL Server：sp_rename
EXEC sp_rename 't_student.name', 'nn', 'COLUMN';

-- Oracle：RENAME COLUMN
ALTER TABLE bbb RENAME COLUMN nnnnn TO hh;

修改欄位資料型別/屬性

-- MySQL：MODIFY 新型別
ALTER TABLE t_book MODIFY name VARCHAR(22);

-- SQL Server：ALTER COLUMN
ALTER TABLE t_book ALTER COLUMN name VARCHAR(22);

-- Oracle：MODIFY
ALTER TABLE t_book MODIFY (name VARCHAR2(22));

約束與索引操作

主鍵

-- 新增主鍵（含自增）
ALTER TABLE tbl_name ADD new_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

-- 刪除主鍵
ALTER TABLE tbl_name DROP PRIMARY KEY;

索引

-- 普通索引
ALTER TABLE tbl_name ADD INDEX idx_name (col1, col2);

-- 唯一索引
ALTER TABLE tbl_name ADD UNIQUE uniq_name (col1);

-- 刪除索引
ALTER TABLE tbl_name DROP INDEX idx_name;

外鍵

-- 新增
ALTER TABLE child ADD CONSTRAINT fk_parent FOREIGN KEY (pid) REFERENCES parent(id) ON DELETE CASCADE;

-- 刪除
ALTER TABLE child DROP FOREIGN KEY fk_parent;

資料操作：UPDATE / DELETE 記錄

UPDATE 修改記錄

UPDATE tbl_name
SET col1 = val1, col2 = val2
WHERE condition;

-- 範例：修正寵物性別
UPDATE pet SET sex='f' WHERE name='Whistler';

警告：無 WHERE 將更新全表，務必加條件或先 SELECT 驗證。

DELETE 刪除記錄

DELETE FROM tbl_name WHERE condition;

-- 刪除特定寵物
DELETE FROM pet WHERE name='Whistler';

-- 清空全表（保留結構、自增不歸零、觸發器觸發）
DELETE FROM pet;

-- 清空全表並重置自增（MyISAM/InnoDB 皆有效、最小日誌、無觸發器）
TRUNCATE TABLE pet;

大表 DDL 線上變更工具（生產環境必備）

• pt-online-schema-change (Percona Toolkit)：建影子表 → 同步資料 → 交換表名，近零鎖定。
• gh-ost (GitHub)：無觸發器、基於 binlog 同步、可暫停/限流。
• MySQL 8.0+ 部分 DDL 支援 Instant DDL（如加欄位、擴展 VARCHAR、增刪虛擬欄位），無鎖、無拷貝。

避坑清單

1. 修改型別可能截斷資料：VARCHAR(100) → VARCHAR(10) 先檢查 MAX(LENGTH(col))。
2. 刪除欄位不可逆：先備份、確認無依賴（視圖/存儲過程/應用代碼）。
3. 外鍵循環依賴：建表順序或 SET FOREIGN_KEY_CHECKS=0 臨時關閉。
4. 字符集變更：ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 會重建表，大表極慢，用線上工具。