MySQL UNIQUE 鍵衝突處理三大技巧：IGNORE、ON DUPLICATE KEY UPDATE、REPLACE

測試環境建立

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

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'

方案 1：INSERT IGNORE —— 靜默跳過

INSERT IGNORE INTO menus (id,label,url) VALUES (4,'Contact us','contactus.html');
-- Query OK, 0 rows affected (0.00 sec)
-- 原資料保持不變

• 適用：批次匯入不確定是否有重複，不想中斷流程。
• 不會觸發 `ON DUPLICATE KEY UPDATE` 邏輯。

方案 2：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.05 sec)
-- 受影響行數 2 = 1 次衝突轉更新

• MySQL 4.1+ 支援。
• 可針對特定欄位更新，保留其他欄位原值。
• 複合唯一鍵任一衝突皆觸發。

方案 3：REPLACE INTO —— 刪除舊行再插入

REPLACE INTO menus (id,label,url) VALUES (4,'Contact us','contactus.html');
-- 相當於 DELETE + INSERT，AUTO_INCREMENT 會重新產生

• 需擁有 DELETE 權限。
• 外鍵關聯時會觸發 CASCADE DELETE，謹慎使用。

效能與選型建議

受影響行數解讀

• INSERT ... ON DUPLICATE KEY UPDATE：新增 1 行、更新 2 行。
• 搭配 `VALUES(col)` 可引用原插入值：UPDATE cnt = cnt + VALUES(cnt)。