萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 防止重複插入的四大核心方案與最佳實踐

前言

業務中頻繁遇到「同一用戶重複下單」、「同一設備重複上報」等場景,需在數據庫層面保證冪等寫入。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 — 靜默忽略衝突

INSERT IGNORE INTO t (email, phone, user_id)
VALUES ('test@163.com', '13800000000', 1001);

行為

  • 若唯一鍵衝突,不報錯、不寫入、返回受影響行數 0。
  • 不觸發 ON DUPLICATE KEY UPDATE、不更新現有行、不產生自增 ID 間隙(InnoDB 仍會預分配自增值導致間隙)。
  • 適用場景:純插入、不關心舊數據、允許靜默丟棄新數據(如日誌去重、幂等導入)。

陷阱

  • 會吞噬所有錯誤(如外鍵失敗、字段過長、NOT NULL 違反),導致靜默寫入失敗卻以為成功。
  • InnoDB 自增 ID 仍會增長,高併發下自增值消耗快。

方案二:REPLACE INTO — 刪除舊行再插入新行

REPLACE INTO t (email, phone, user_id)
VALUES ('test@163.com', '13800000000', 1001);

行為

  • 嘗試 INSERT → 唯一鍵衝突 → DELETE 舊行 → 再次 INSERT 新行。
  • 返回值 = 刪除行數 + 插入行數:1 表示純插入,≥2 表示替換發生。
  • 所有列按新行值重寫,未指定列按默認值填充(而非保留舊值)。

適用場景

  • 需要完全用新行覆蓋舊行(如配置表全量刷新、用戶資料全量同步)。

嚴重缺陷

  • 觸發器陷阱:先觸發 BEFORE DELETE、AFTER DELETE,再觸發 BEFORE INSERT、AFTER INSERT,業務邏輯可能執行兩次。
  • 外鍵級聯刪除風險:若被其他表引用且定義 ON DELETE CASCADE,會誤刪關聯數據。
  • 自增 ID 變更:舊行刪除、新行插入,主鍵 ID 改變,破壞引用完整性。
  • 性能開銷:雙倍寫入(Delete + Insert),Binlog 記錄兩條事件,主從延遲加大。

方案三:INSERT ... ON DUPLICATE KEY UPDATE — 精準更新衝突列(推薦)

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();

核心優勢

  • 列級精準控制:僅更新指定列,保留其餘列舊值。
  • 原子性:單條語句完成「查找-決策-寫入」,無競態條件。
  • 返回值語義清晰:1=新增,2=更新,0=IGNORE 情況(配合 IGNORE 時)。
  • 支持 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)。
  • 性能遠低於原生語法,僅用於極其複雜邏輯無法用 SQL 表達時。

選型決策表

業務需求推薦方案理由
純去重,不更新舊數據,允許靜默丟棄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」雙重防線。

copyright © 萬盛學電腦網 all rights reserved