MySQL 資料庫遷移與結構變更必備指令集：匯出匯入、增刪改欄位全攻略

資料庫上線、環境遷移、Schema 演進是開發與 DBA 的高頻作業。本文彙整 mysqldump 邏輯備份、source 還原、ALTER TABLE 結構變更 (增欄位、改型別、加自增主鍵) 的標準指令與生產環境避坑要點，建議收藏備查。

一、邏輯備份與還原 (mysqldump / source)

1. 匯出含資料的完整資料庫

mysqldump -u root -p --databases abc > /backup/abc_full_$(date +%F).sql
# 輸入密碼後生成：建庫語句 + 建表語句 + INSERT 資料

2. 匯出單表 (含資料 / 結構)

# 含資料
mysqldump -u root -p abc users > /backup/users_data.sql

# 僅結構 (無資料) - 適用 Schema 同步、Code Review
mysqldump -u root -p -d abc users > /backup/users_schema.sql

3. 匯出多資料庫 / 全例項

# 指定多庫
mysqldump -u root -p --databases db1 db2 > multi_db.sql

# 全例項 (含 mysql 系統庫、使用者權限)
mysqldump -u root -p --all-databases --single-transaction --master-data=2 > full_instance.sql

4. 高效還原 (source / mysql 管道)

# 方式一：進入 mysql client 後執行 (適合交互式)
mysql -u root -p
USE abc;
SOURCE /backup/abc_full.sql;

# 方式二：管道直導 (適合自動化腳本，更快)
mysql -u root -p abc < /backup/abc_full.sql

# 方式三：壓縮檔案直接導入 (節省磁碟 IO)
gunzip < abc_full.sql.gz | mysql -u root -p abc

二、ALTER TABLE 結構變更實戰

1. 新增欄位 (ADD COLUMN)

-- 末尾新增 NOT NULL 欄位，需給預設值
ALTER TABLE `orders` 
ADD COLUMN `status` TINYINT NOT NULL DEFAULT 0 COMMENT '訂單狀態: 0待付款 1已付款 2已發貨';

-- 指定位置新增 (AFTER/FIRST)
ALTER TABLE `users` 
ADD COLUMN `phone` VARCHAR(20) AFTER `email`;

2. 修改欄位定義 (MODIFY / CHANGE)

-- MODIFY：僅改型別/屬性，欄位名不變
ALTER TABLE `products` 
MODIFY COLUMN `price` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '單價(元)';

-- CHANGE：改名 + 改型別 (舊名 新名 新定義)
ALTER TABLE `users` 
CHANGE COLUMN `nick_name` `nickname` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '暱稱';

3. 新增自增主鍵 (避坑指南)

-- 空表直接加
ALTER TABLE `logs` 
ADD COLUMN `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

-- 非空表：必須先加可 NULL 欄位 → 回填值 → 改 NOT NULL + AUTO_INCREMENT
ALTER TABLE `old_table` ADD COLUMN `id` BIGINT UNSIGNED NULL FIRST;
UPDATE `old_table` SET `id` = 0; -- 或按業務邏輯賦值
ALTER TABLE `old_table` MODIFY COLUMN `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

4. 刪除欄位 / 索引 / 外鍵

ALTER TABLE `t1` DROP COLUMN `obsolete_col`;
ALTER TABLE `t1` DROP INDEX `idx_name`;
ALTER TABLE `t1` DROP FOREIGN KEY `fk_id`; -- 先查 SHOW CREATE TABLE 確認 FK 名稱

三、生產環境變更黃金法則 (必讀)

四、常見陷阱快速查核

• 字元集不匹配：新增欄位未指定 CHARSET utf8mb4，導致存入 Emoji 報錯。建議表級/庫級預設 utf8mb4。
• 預設值陷阱：NOT NULL 欄位無 DEFAULT，舊版 MySQL (5.6-) 嚴格模式下會報錯或塞入「零值」 (0, '', '0000-00-00')。
• 外鍵死鎖：大表加外鍵會全表掃描鎖定，生產嚴禁直接加 FK，應用層保證一致性或用觸發器異步校驗。
• 自增 ID 耗盡：INT 上限 21 億，高併發大表必用 BIGINT UNSIGNED (上限 1.8e19)。

掌握這套指令體系與變更治理流程，資料庫 Schema 演進將從「高風險手術」變為「標準化交付」。