MySQL UNIQUE 鍵衝突解決方案：IGNORE 與 ON DUPLICATE KEY UPDATE 實戰對比

場景重現

CREATE TABLE menus (
    id TINYINT(4) NOT NULL AUTO_INCREMENT,
    label VARCHAR(10),
    url VARCHAR(20),
    UNIQUE KEY (id)
);

INSERT INTO menus (label,url) VALUES
('Home','home.html'),
('About us','aboutus.html'),
('Services','services.html'),
('Feedback','feedback.html');

-- 嘗試插入重複 id=4
INSERT INTO menus (id,label,url) VALUES (4,'Contact us','contactus.html');
-- ERROR 1062 (23000): Duplicate entry '4' for key 'id'

方案一：INSERT IGNORE 靜默跳過

INSERT IGNORE INTO menus (id,label,url) VALUES (4,'Contact us','contactus.html');
-- Query OK, 0 rows affected (0.00 sec)

SELECT * FROM menus;
-- id=4 仍為 'Feedback'，新數據被忽略

特點：不報錯、不寫入、不影響自增值、批量導入時單行失敗不中斷整體。

方案二：ON DUPLICATE KEY UPDATE 覆蓋更新

INSERT INTO menus (id,label,url) VALUES (4,'Contact us','contactus.html')
ON DUPLICATE KEY UPDATE label='Contact us', url='contactus.html';
-- Query OK, 2 rows affected (0.01 sec)

SELECT * FROM menus;
-- id=4 已更新為 'Contact us'

特點：檢測到唯一鍵衝突自動轉為 UPDATE，rows affected 返回 2（1 insert + 1 update）。

深度對比

進階用法：VALUES() 函數引用新值

INSERT INTO menus (id,label,url) VALUES (4,'New Label','new.html')
ON DUPLICATE KEY UPDATE 
    label=VALUES(label), 
    url=VALUES(url),
    update_time=NOW();

避免重複書寫值，維護性更強。

批量導入最佳實踐

-- 方案 A：全量覆蓋同步
INSERT INTO target_table (id, col1, col2)
SELECT id, col1, col2 FROM source_table
ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2);

-- 方案 B：增量同步（僅更新變更行）
INSERT INTO target_table (id, col1, col2, updated_at)
SELECT id, col1, col2, NOW() FROM source_table
WHERE updated_at > '2024-01-01'
ON DUPLICATE KEY UPDATE 
    col1=VALUES(col1), 
    col2=VALUES(col2),
    updated_at=VALUES(updated_at);

避坑指南

1. 多唯一鍵表：任一唯一鍵衝突均觸發 UPDATE，需確認業務邏輯
2. 外鍵關聯：UPDATE 可能觸發外鍵檢查，影響性能
3. 複製主從：ON DUPLICATE KEY UPDATE 在 Row 模式下正確複製，Statement 模式需注意
4. 自增主鍵：衝突不消耗自增值，但頻繁衝突可能導致自增值增長過快

核心選擇：**業務要求「以新為準」用 ON DUPLICATE KEY UPDATE，「以舊為準/去重」用 INSERT IGNORE**。兩者配合批量操作，優雅解決 99% 的唯一鍵衝突場景。