業務高頻場景:「數據存在則更新,不存在則插入」(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 INTO test_tab (name, age) VALUES ('zhangsan', 24);
| 場景 | 行為 | 影響行數 | 返回 |
|---|---|---|---|
| 鍵不存在 | 插入新行 | 1 | OK |
| 鍵存在 | 忽略,不更新現有行 | 0 | OK(Warning) |
特點:
ROW_COUNT() 判斷)ON UPDATE 觸發器INSERT INTO test_tab (name, age) VALUES ('zhangsan', 50)
ON DUPLICATE KEY UPDATE age = 50;
| 場景 | 行為 | 影響行數 | 返回 |
|---|---|---|---|
| 鍵不存在 | 插入新行 | 1 | OK |
| 鍵存在 | 更新現有行 | 2 | OK |
| 鍵存在但值相同 | 無實際變更 | 0 | OK |
| 場景 | 行為 | 影響行數 | 副作用 |
|---|---|---|---|
| 鍵不存在 | 插入新行 | 1 | 無 |
| 鍵存在 | DELETE 舊行 + INSERT 新行 | 2 | 自增 ID 變更、外鍵級聯刪除、觸發器 DELETE+INSERT 雙觸發 |
⚠️ 致命缺陷:
ON DELETE CASCADE 會誤刪關聯資料| 需求 | 推薦寫法 | 禁用寫法 |
|---|---|---|
| 僅補全缺失資料,不覆蓋現有 | INSERT IGNORE | ON DUPLICATE KEY UPDATE / REPLACE |
| 標準 Upsert:存在則更新業務欄位 | ON DUPLICATE KEY UPDATE | REPLACE INTO |
| 需保持自增 ID 不變 | ON DUPLICATE KEY UPDATE | REPLACE INTO |
| 表有外鍵被引用 | ON DUPLICATE KEY UPDATE | REPLACE INTO |
| 需觸發 UPDATE 觸發器 | ON DUPLICATE KEY UPDATE | INSERT IGNORE / REPLACE |
| 批量導入允許靜默跳過錯誤 | INSERT IGNORE | -- |
| 寫法 | 平均延遲 | 鎖持有時間 | Binlog 量 |
|---|---|---|---|
| INSERT IGNORE | 0.15ms | 最短 | 最小(僅插入時) |
| ON DUPLICATE KEY UPDATE | 0.18ms | 短 | 中等(含更新前映像) |
| REPLACE INTO | 0.35ms | 較長 | 最大(DELETE+INSERT 兩事件) |
ON DUPLICATE KEY UPDATE 是 99% Upsert 場景的標準答案:語義精確、效能最優、副作用最小、支援條件更新。INSERT IGNORE 專用於「冪等補全」;REPLACE INTO **生產環境嚴禁使用**(除非極其清楚其 DELETE+INSERT 語義且無外鍵/觸發器/自增 ID 依賴)。