MySQL 避免重複插入的三大核心方案深度解析

前提條件

表必須擁有 主鍵 或 唯一索引，否則無法判斷「重複」。

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

INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`)
VALUES ('test9@163.com', '99999', '9999');

• 重複時不報錯，返回受影響行數 0。
• 適用：大量導入、不關心是否真正寫入、效能優先。
• 跨表去重：INSERT IGNORE INTO t1 (name) SELECT name FROM t2;

方案二：REPLACE INTO —— 刪舊插新

REPLACE INTO `table_name` (`col_name`, ...) VALUES (...);
REPLACE INTO `table_name` (`col_name`, ...) SELECT ...;
REPLACE INTO `table_name` SET `col_name` = 'value', ...;

執行邏輯：

1. 嘗試插入新行。
2. 若主鍵/唯一鍵衝突 → 刪除舊行 → 再次插入新行。

返回值： 受影響行數 = 刪除行數 + 插入行數。
1 表示純插入，≥2 表示發生替換。

注意： 會觸發 DELETE + INSERT 觸發器，自增 ID 會改變。

方案三：INSERT ... ON DUPLICATE KEY UPDATE —— 精準更新

INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3)
ON DUPLICATE KEY UPDATE `c` = `c` + 1;

等價於：

UPDATE `table` SET `c` = `c` + 1 WHERE `a` = 1;

多行插入技巧：

INSERT INTO `table` (`a`, `b`, `c`) VALUES (1,2,3), (4,5,6)
ON DUPLICATE KEY UPDATE `c` = VALUES(`a`) + VALUES(`b`);

VALUES(col) 可引用插入值，極適合批次 upsert。

返回值判斷

實戰範例：跨表同步狀態

INSERT INTO `table_name1` (`title`, `first_name`, `last_name`, `email`, `phone`, `user_id`, `role_id`, `status`, `campaign_id`)
SELECT '', '', '', `table_name2`.`email`, `table_name2`.`phone`, NULL, NULL, 'pending', 29
FROM `table_name2`
WHERE `table_name2`.`status` = 1
ON DUPLICATE KEY UPDATE `table_name1`.`status` = 'pending';

關鍵字補充

• DELAYED：非阻塞插入，犧牲一致性換吞吐。
• IGNORE：僅關注主鍵/唯一鍵不存在則插入，存在則忽略。

陷阱提醒

MySQL 中 UNIQUE 索引不包含 NULL。以下可重複插入：

INSERT INTO `test` (`a`) VALUES (NULL); -- 可重複執行

聯合唯一索引同理，任何欄位為 NULL 都不會觸發衝突。

選型建議