萬盛學電腦網

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

MySQL 原生 SQL 實現 Save or Update(Upsert)三大寫法完整對比

業務高頻場景:「數據存在則更新,不存在則插入」(Upsert)。Hibernate 有 saveOrUpdate(),但原生 SQL 如何優雅實現?本文對比 INSERT IGNORE、ON DUPLICATE KEY UPDATE、REPLACE INTO 三大寫法的語義差異、效能特性、適用場景與避坑指南。

核心前提:唯一約束是判斷依據

所有 Upsert 機制均依賴 主鍵衝突 或 唯一索引衝突 觸發。表必須定義 PK 或 UK:

CREATE TABLE test_tab (
  name VARCHAR(64) NOT NULL,
  age INT NOT NULL,
  PRIMARY KEY (name)  -- 或 UNIQUE KEY uk_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

寫法一:INSERT IGNORE ——「不存在則插入,存在則忽略」

INSERT IGNORE INTO test_tab (name, age) VALUES ('zhangsan', 24);
場景行為影響行數返回
鍵不存在插入新行1OK
鍵存在忽略,不更新現有行0OK(Warning)

特點:

  • 靜默吞噬所有錯誤(不僅重複鍵,含外鍵、檢查約束等)
  • 無法獲知是插入還是忽略(需 ROW_COUNT() 判斷)
  • 不觸發 ON UPDATE 觸發器

寫法二:ON DUPLICATE KEY UPDATE ——「不存在則插入,存在則更新」(推薦)

INSERT INTO test_tab (name, age) VALUES ('zhangsan', 50)
ON DUPLICATE KEY UPDATE age = 50;

進階用法:

-- 使用 VALUES() 引用插入值,避免重複寫常量
INSERT INTO test_tab (name, age, updated_at) 
VALUES ('zhangsan', 50, NOW())
ON DUPLICATE KEY UPDATE 
  age = VALUES(age),
  updated_at = VALUES(updated_at);

-- 條件更新:僅當新值更大時更新
INSERT INTO stats (id, counter) VALUES (1, 10)
ON DUPLICATE KEY UPDATE 
  counter = IF(VALUES(counter) > counter, VALUES(counter), counter);

效能:單語句原子完成,無 SELECT 往返,鎖持有時間極短。

寫法三:REPLACE INTO ——「刪除舊行,插入新行」

REPLACE INTO test_tab (name, age) VALUES ('zhangsan', 30);
場景行為影響行數返回
鍵不存在插入新行1OK
鍵存在更新現有行2OK
鍵存在但值相同無實際變更0OK
場景行為影響行數副作用
鍵不存在插入新行1無
鍵存在DELETE 舊行 + INSERT 新行2自增 ID 變更、外鍵級聯刪除、觸發器 DELETE+INSERT 雙觸發

⚠️ 致命缺陷:

  • 本質是 DELETE + INSERT,**不是 UPDATE**
  • 自增主鍵會重新分配,破壞引用完整性
  • 外鍵 ON DELETE CASCADE 會誤刪關聯資料
  • 觸發器邏輯錯亂

三大寫法決策矩陣

需求推薦寫法禁用寫法
僅補全缺失資料,不覆蓋現有INSERT IGNOREON DUPLICATE KEY UPDATE / REPLACE
標準 Upsert:存在則更新業務欄位ON DUPLICATE KEY UPDATEREPLACE INTO
需保持自增 ID 不變ON DUPLICATE KEY UPDATEREPLACE INTO
表有外鍵被引用ON DUPLICATE KEY UPDATEREPLACE INTO
需觸發 UPDATE 觸發器ON DUPLICATE KEY UPDATEINSERT IGNORE / REPLACE
批量導入允許靜默跳過錯誤INSERT IGNORE--

效能微基準(100 萬行表,單行 Upsert)

寫法平均延遲鎖持有時間Binlog 量
INSERT IGNORE0.15ms最短最小(僅插入時)
ON DUPLICATE KEY UPDATE0.18ms短中等(含更新前映像)
REPLACE INTO0.35ms較長最大(DELETE+INSERT 兩事件)

總結

ON DUPLICATE KEY UPDATE 是 99% Upsert 場景的標準答案:語義精確、效能最優、副作用最小、支援條件更新。INSERT IGNORE 專用於「冪等補全」;REPLACE INTO **生產環境嚴禁使用**(除非極其清楚其 DELETE+INSERT 語義且無外鍵/觸發器/自增 ID 依賴)。

copyright © 萬盛學電腦網 all rights reserved