業務中頻繁遇到「同一用戶重複下單」、「同一設備重複上報」等場景,需在數據庫層面保證冪等寫入。MySQL 提供 INSERT IGNORE、REPLACE INTO、ON DUPLICATE KEY UPDATE 三大原生語法,結合唯一約束可實現高效去重。本文深度解析三大方案機制、差異、陷阱與選型指南,並附帶應用層雙重校驗模式。
所有去重方案均依賴 主鍵 或 唯一索引 判斷衝突。無唯一約束時,INSERT IGNORE 等同普通 INSERT,REPLACE 等同 INSERT,ON DUPLICATE KEY UPDATE 永遠不觸發 UPDATE。
ALTER TABLE t ADD UNIQUE KEY uk_phone (phone);
-- 或建表時指定
CREATE TABLE t (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
phone VARCHAR(20) NOT NULL,
UNIQUE KEY uk_phone (phone)
) ENGINE=InnoDB;
INSERT IGNORE INTO t (email, phone, user_id)
VALUES ('test@163.com', '13800000000', 1001);
ON DUPLICATE KEY UPDATE、不更新現有行、不產生自增 ID 間隙(InnoDB 仍會預分配自增值導致間隙)。REPLACE INTO t (email, phone, user_id)
VALUES ('test@163.com', '13800000000', 1001);
刪除行數 + 插入行數:1 表示純插入,≥2 表示替換發生。BEFORE DELETE、AFTER DELETE,再觸發 BEFORE INSERT、AFTER INSERT,業務邏輯可能執行兩次。ON DELETE CASCADE,會誤刪關聯數據。INSERT INTO t (email, phone, user_id, login_count)
VALUES ('test@163.com', '13800000000', 1001, 1)
ON DUPLICATE KEY UPDATE
login_count = login_count + 1,
last_login = NOW();
VALUES(col) 引用插入值:UPDATE c = VALUES(a) + VALUES(b)。INSERT ... VALUES (...), (...) ON DUPLICATE KEY UPDATE ...。-- 僅在衝突時更新時間戳,不修改業務字段
INSERT INTO t (id, data) VALUES (1, 'new')
ON DUPLICATE KEY UPDATE updated_at = NOW();
-- 利用 LAST_INSERT_ID(expr) 使更新時也能獲取自增 ID
INSERT INTO t (id, val) VALUES (NULL, 100)
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), val = 200;
SELECT LAST_INSERT_ID(); -- 返回衝突行的 id
-- 伪代碼
SELECT id FROM t WHERE phone = '13800000000' FOR UPDATE; -- 行鎖
IF NOT EXISTS THEN
INSERT INTO t (phone, ...) VALUES ('13800000000', ...);
ELSE
UPDATE t SET ... WHERE phone = '13800000000';
END IF;
SELECT ... WHERE ... 不加鎖會有競態窗口,必須 FOR UPDATE 或使用分布式鎖(Redis SETNX)。| 業務需求 | 推薦方案 | 理由 |
|---|---|---|
| 純去重,不更新舊數據,允許靜默丟棄 | INSERT IGNORE | 最簡潔、性能最好 |
| 全量覆蓋舊行(所有列以新值為準) | REPLACE INTO | 語義直觀,但慎用於有外鍵/觸發器的表 |
| 衝突時部分列增量更新(計數器、時間戳、狀態機) | ON DUPLICATE KEY UPDATE | 功能最全、最安全、生產環境首選 |
| 批量導入數萬行,需高效去重 | ON DUPLICATE KEY UPDATE + 多行 VALUES | 單次交互、減少網絡往返 |
-- 保留每組重複項中 id 最小的一行
DELETE t1 FROM t t1
JOIN (
SELECT MIN(id) AS min_id, phone
FROM t
GROUP BY phone
HAVING COUNT(*) > 1
) t2 ON t1.phone = t2.phone AND t1.id > t2.min_id;
MySQL 原生去重三板斧中,INSERT ... ON DUPLICATE KEY UPDATE 功能最完整、副作用最小,是生產環境冪等寫入的首選。務必建立唯一約束、理解各方案對自增 ID、觸發器、外鍵、Binlog 的影響,並建立「數據庫層去重 + 應用層幂等 Key」雙重防線。