MySQL UPDATE 主鍵衝突深度分析：MyISAM vs InnoDB 儲存引擎行為差異

問題重現

資料表 a，主鍵 id，6 筆資料：

+----+------+
| id | id2  |
+----+------+
|  2 |    2 |
|  3 |    3 |
|  5 |    5 |
|  4 |    4 |
|  6 |    6 |
|  7 |    7 |
+----+------+

執行 UPDATE a SET id = id - 1; 報錯：

ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

前兩筆成功（2→1, 3→2），第三筆 5→4 與現有 id=4 衝突。

解決方案：ORDER BY 控制更新順序

UPDATE a SET id = id - 1 ORDER BY id;

依主鍵遞增更新，避免衝突，6 筆全數成功。

儲存引擎行為差異分析

MyISAM：堆積組織表 (Heap Organized Table, HOT)

• 索引：B-Tree
• 資料顯示順序：隨機（非主鍵順序）
• 無指定 ORDER BY 時，依儲存順序更新 → 易發生主鍵衝突

InnoDB：索引組織表 (Index Organized Table, IOT)

• 索引：Clustered Index（叢集索引）
• 資料依主鍵順序儲存
• 無 ORDER BY 時，預設依主鍵順序更新 → 天然避免衝突
• 無顯式主鍵時內建隱藏主鍵

實驗驗證

MyISAM 表

CREATE TABLE a (id INT UNSIGNED PRIMARY KEY, id2 INT UNSIGNED) ENGINE=MyISAM;
-- 插入無序資料
UPDATE a SET id = id - 1;           -- 失敗
UPDATE a SET id = id - 1 ORDER BY id; -- 成功

轉換為 InnoDB

ALTER TABLE a ENGINE = InnoDB;
-- 資料自動依 id 排序顯示
UPDATE a SET id = id - 1; -- 直接成功，無需 ORDER BY

重新插入無序資料測試

TRUNCATE TABLE a;
INSERT INTO a VALUES (2,2),(3,3),(5,5),(4,4),(6,6),(7,7);
SELECT * FROM a; -- 仍依 id 排序顯示
UPDATE a SET id = id - 1; -- 成功

核心結論

最佳實務建議

• 更新主鍵或唯一索引欄位時，習慣加上 ORDER BY 明確指定順序
• InnoDB 為預設引擎（MySQL 5.5+），利用其叢集索引特性減少此類問題
• 大量資料主鍵異動建議：建新表 → 匯入轉換後資料 → 重命名替換