SQL Server 與 MySQL 修改自增欄位值完整方案

SQL Server 平台

鐵律：UPDATE 直接修改 IDENTITY 欄位會報錯「無法更新識別欄位」。

方案 A：移除再重建 IDENTITY（需停機維護窗口）

1. ALTER TABLE t DROP COLUMN id;（或用 SSMS 設計師取消 Identity）
2. 更新數值
3. ALTER TABLE t ADD id INT IDENTITY(1,1);

缺點：大表鎖表久、需處理外鍵、權限、索引重建。

方案 B：刪除重插（適用少量資料）

1. 將目標列腳本化為 INSERT 陳述式（指定 ID）。
2. DELETE 原列。
3. SET IDENTITY_INSERT t ON; INSERT ...; SET IDENTITY_INSERT t OFF;

方案 C：業務層重新發布（最簡單）

運營人員刪除舊資料、重新錄入，避免資料庫層面風險。

MySQL 平台

MySQL AUTO_INCREMENT 欄位直接 UPDATE 交換值會因主鍵衝突失敗。採用「中間值法」三步走：

InnoDB 引擎（必須為單列主鍵）

-- 交換 id=1 和 id=2
UPDATE t SET id = 0 WHERE id = 1;  -- 1→0（中間值）
UPDATE t SET id = 1 WHERE id = 2;  -- 2→1
UPDATE t SET id = 2 WHERE id = 0;  -- 0→2

測試結果：

MyISAM 引擎（可為複合主鍵一部分）

同樣三步法，但實測發現：第一步 UPDATE ... SET id=0 WHERE id=1 雖顯示成功，後續語句卻報錯並回滾。原因未深究，建議改用方案 A（移除 AUTO_INCREMENT → 更新 → 重建）或方案 C。

關鍵提醒

• 中間值選 ≤0 或 > 現有最大值，避免衝突。
• 交易包裹三步，確保原子性。
• 涉及外鍵時必須同步更新子表，或暫時停用 FK 檢查（SET FOREIGN_KEY_CHECKS=0）。
• 生產庫嚴禁直接手工修改自增值，務必經測試、備份、變更流程。