MySQL 修改自增主鍵 INT 轉 CHAR 類型完整操作指南

業務變更需將自增 INT 主鍵改為 CHAR(18)（如雪花算法 ID），且表內已有數據不能重建。本文提供零停機、分步驟、可回滾的變更方案，並分析外鍵、觸發器、複制等依賴物件的處理要點。

一、變更前檢查清單

• 確認表大小、行數、磁碟空間（需 ≥ 3× 表大小）。
• 檢查引用該主鍵的外鍵：SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE REFERENCED_TABLE_NAME='t';
• 檢查觸發器、存儲過程、視圖、應用代碼對主鍵型別的依賴。
• 確認複制環境：從庫同步延遲、binlog_format=ROW。
• 準備完整備份並驗證可恢復。

二、標準變更流程（需鎖表，適用低峰小表）

-- 1. 移除 AUTO_INCREMENT 屬性
ALTER TABLE `t` MODIFY `id` INT NOT NULL;

-- 2. 刪除主鍵約束
ALTER TABLE `t` DROP PRIMARY KEY;

-- 3. 修改欄位型別（INT -> CHAR(18)）
ALTER TABLE `t` MODIFY `id` CHAR(18) NOT NULL;

-- 4. 重新建立主鍵
ALTER TABLE `t` ADD PRIMARY KEY (`id`);

-- 5. 更新應用層生成邏輯（雪花 ID / UUID 等），不再依賴自增。

三、大表零停機方案：pt-online-schema-change

pt-online-schema-change \
    --alter "MODIFY id CHAR(18) NOT NULL, DROP PRIMARY KEY, ADD PRIMARY KEY (id)" \
    --alter-foreign-keys-method=auto \
    --max-load "Threads_running=100" \
    --critical-load "Threads_running=200" \
    --chunk-time 0.5 \
    --pause-file /tmp/pt-osc-pause \
    --execute \
    D=db,t=t

⚠️ pt-osc 會建立觸發器、影子表、重命名表，步驟 3/5/7 需元數據鎖（MDL），需配合 --max-load、--tries 參數避免長事務阻塞。

四、外鍵依賴表的級聯處理

-- 子表外鍵欄位同步修改
-- 1. 刪除外鍵
ALTER TABLE child DROP FOREIGN KEY fk_child_parent;

-- 2. 修改子表欄位型別
ALTER TABLE child MODIFY parent_id CHAR(18) NOT NULL;

-- 3. 重建外鍵（父表主鍵已為 CHAR(18)）
ALTER TABLE child ADD CONSTRAINT fk_child_parent
    FOREIGN KEY (parent_id) REFERENCES parent(id)
    ON DELETE RESTRICT ON UPDATE CASCADE;

五、驗證與回滾

1. 驗證資料完整性：SELECT COUNT(*), MIN(id), MAX(id) FROM t;
2. 驗證應用讀寫正常：插入新記錄、查詢舊記錄、關聯查詢。
3. 驗證從庫同步：SHOW SLAVE STATUS\G 確認 Seconds_Behind_Master=0。
4. 回滾方案：若發現嚴重問題，立即 RENAME TABLE t_new TO t_old, t_old TO t（pt-osc 保留舊表）或從備份恢復。

六、常見坑與避坑

主鍵型別變更是「心臟手術」，務必在測試環境全流程演練、量化鎖表時間、驗證應用兼容性，方可上生產。pt-osc 是大表的標準選擇，小表低峰直改更簡潔。