萬盛學電腦網

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

MySQL 防止重複插入的三種核心方案詳解

MySQL 防重複插入/更新:三大核心方案深度解析

業務開發中常需「不存在則插入,存在則更新」的 upsert 語義。MySQL 提供三種主要實現方式,本文詳細對比其語法、行為差異與適用場景。

方案一:INSERT ... ON DUPLICATE KEY UPDATE (推薦)

MySQL 專有擴展,基於主鍵/唯一索引衝突判斷,性能最佳。

基本語法

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

等價邏輯

若 a 為唯一鍵且值 1 已存在,等價於:

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

進階用法

-- 多行插入,VALUES() 引用插入值
INSERT INTO table (a,b,c) VALUES (1,2,3), (4,5,6)
ON DUPLICATE KEY UPDATE c = VALUES(a) + VALUES(b);
-- 第1行衝突:c=1+2=3;第2行衝突:c=4+5=9

-- 配合 AUTO_INCREMENT 保持 LAST_INSERT_ID() 有效
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), c = 3;

注意事項

  • 表必須有 主鍵或唯一索引,否則無法觸發衝突檢測
  • 多個唯一索引時,任一衝突即觸發更新,行為可能不符合預期
  • DELAYED 選項會被忽略
  • 不支持 WHERE 子句進一步過濾

方案二:INSERT ... SELECT ... WHERE NOT EXISTS (通用標準 SQL)

跨數據庫兼容性好,但性能較差,需子查詢掃描。

批量插入示例

INSERT INTO clients (client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE NOT EXISTS (
  SELECT 1 FROM clients WHERE clients.client_id = suppliers.supplier_id
);

單行插入示例

INSERT INTO clients (client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE NOT EXISTS (
  SELECT 1 FROM clients WHERE client_id = 10345
);

dual 是虛擬表,用於無表單行查詢。

方案三:REPLACE INTO (暴力覆蓋)

語法簡單但行為激進:衝突時先刪除舊行再插入新行。

語法形式

REPLACE INTO table_name (col1, col2) VALUES (val1, val2);
REPLACE INTO table_name (col1, col2) SELECT ...;
REPLACE INTO table_name SET col1 = val1, col2 = val2;

執行算法

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

返回值含義

  • 1:純插入(無衝突)
  • >1:發生替換(刪除 + 插入行數之和)

⚠️ 關鍵風險

  • 自增 ID 會改變:刪除重插導致主鍵值變更,破壞外鍵引用
  • 觸發器行為異常:先觸發 DELETE 觸發器,再觸發 INSERT 觸發器
  • 權限要求高:需 DELETE + INSERT 權限
  • 無主鍵/唯一索引時退化為純 INSERT

方案對比總結

維度ON DUPLICATE KEY UPDATEINSERT ... WHERE NOT EXISTSREPLACE INTO
性能⭐⭐⭐ 最優⭐⭐ 子查詢開銷⭐⭐ 刪除+插入
保持主鍵值✅ 是✅ 是❌ 變更
觸發器友好✅ 僅 UPDATE✅ 僅 INSERT❌ DELETE+INSERT
標準 SQL❌ MySQL 專有✅ 跨庫兼容✅ 標準 SQL
適用場景高併發 upsert、計數器更新異構遷移、無唯一索引表全行覆蓋、無外鍵依賴

SQL Server 對應寫法 (MERGE)

IF NOT EXISTS (SELECT 1 FROM t WHERE phone = '1')
  INSERT INTO t (phone, update_time) VALUES ('1', GETDATE());
ELSE
  UPDATE t SET update_time = GETDATE() WHERE phone = '1';

-- 或使用 MERGE (SQL Server 2008+)
MERGE INTO t AS target
USING (VALUES ('1', GETDATE())) AS source (phone, update_time)
ON target.phone = source.phone
WHEN MATCHED THEN UPDATE SET update_time = source.update_time
WHEN NOT MATCHED THEN INSERT (phone, update_time) VALUES (source.phone, source.update_time);

最佳實踐建議

  1. 首選 ON DUPLICATE KEY UPDATE:性能最好、語義清晰、保持主鍵穩定
  2. 確保表有合適的唯一索引覆盖業務鍵
  3. 避免在高併發場景使用 REPLACE INTO
  4. 批量操作時考慮分批提交,減少鎖持有時間
copyright © 萬盛學電腦網 all rights reserved