萬盛學電腦網

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

MySQL Upsert 核心技術:REPLACE INTO 與 INSERT ... ON DUPLICATE KEY UPDATE 深度對比與選型指南

「存在即更新,不存在即插入」(Upsert)是高併發寫入場景的核心需求。MySQL 提供 REPLACE INTO 和 INSERT ... ON DUPLICATE KEY UPDATE 兩大原生方案,本文深度剖析機制差異、效能特徵與選型陷阱。

核心前提:唯一約束是觸發條件

兩種語法必須依賴表上的 主鍵 或 唯一索引 判斷衝突。無唯一約束 = 無衝突檢測 = 純插入行為。

方案一:REPLACE INTO - 刪除重插模式

語法與執行流程

REPLACE [LOW_PRIORITY | DELAYED] INTO tbl_name [(col_list)] VALUES (val_list);
-- 或
REPLACE INTO tbl_name SET col1=val1, col2=val2...;
-- 或
REPLACE INTO tbl_name SELECT ...;

內部執行邏輯:

  1. 嘗試插入新行
  2. 若主鍵/唯一鍵衝突 → 刪除所有衝突行 → 重新插入新行

影響行數語義

  • 返回 1:純插入(無衝突)
  • 返回 2:1 刪除 + 1 插入(單鍵衝突)
  • 返回 N+1:N 刪除 + 1 插入(多唯一索引衝突不同行)

實戰測試驗證

CREATE TABLE `t_test` (
    `ikey` INT(11) NOT NULL DEFAULT 0,
    `value` VARCHAR(21) NOT NULL DEFAULT '',
    `icount` INT(11) NOT NULL DEFAULT 0,
    PRIMARY KEY (`ikey`),
    UNIQUE KEY `uk_value` (`value`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;

-- 初始資料
INSERT INTO `t_test` VALUES (1, 'a', 10), (2, 'b', 20);

-- REPLACE 衝突主鍵
REPLACE INTO `t_test` SET `ikey`=1, `value`='c', `icount`=0;
-- 結果:刪除 (1,'a',10) → 插入 (1,'c',0) → 影響行數 2

-- REPLACE 衝突唯一鍵
REPLACE INTO `t_test` SET `ikey`=3, `value`='a', `icount`=0;
-- 結果:刪除 (1,'c',0) → 插入 (3,'a',0) → 影響行數 2

-- REPLACE 同時衝突主鍵+唯一鍵(不同行)
REPLACE INTO `t_test` SET `ikey`=1, `value`='b', `icount`=0;
-- 結果:刪除 (1,'c',0) 和 (2,'b',20) → 插入 (1,'b',0) → 影響行數 3 ⚠️

方案二:INSERT ... ON DUPLICATE KEY UPDATE - 原地更新模式

語法與執行流程

INSERT INTO tbl_name (col_list) VALUES (val_list)
ON DUPLICATE KEY UPDATE col1=val1, col2=val2...;

內部執行邏輯:

  1. 嘗試插入新行
  2. 若主鍵/唯一鍵衝突 → 更新衝突行(僅影響第一個匹配的唯一鍵衝突行)

影響行數語義

  • 返回 1:純插入
  • 返回 2:更新現有行
  • 返回 0:ON DUPLICATE KEY UPDATE 子句未實際修改值(如 SET col=col)

實戰測試驗證

-- 同樣初始資料
INSERT INTO `t_test` SET `ikey`=1, `value`='c', `icount`=0
ON DUPLICATE KEY UPDATE `icount`=`icount`+1;
-- 結果:更新 (1,'a',10) → (1,'a',11) → 影響行數 2

-- 基於原值運算(計數器模式)
INSERT INTO `t_test` SET `ikey`=1, `value`='a', `icount`=1
ON DUPLICATE KEY UPDATE `icount`=`icount`+1;
-- 衝突時 icount 遞增,不衝突則插入 icount=1

深度對比矩陣

維度REPLACE INTOINSERT ... ON DUPLICATE KEY UPDATE
核心機制DELETE + INSERTUPDATE (原地修改)
自增主鍵行為重新生成新 ID保留原 ID
未指定欄位值重置為預設值/NULL保留原值
多唯一索引衝突刪除所有衝突行僅更新第一個匹配行
觸發器觸發BEFORE/AFTER DELETE + INSERTBEFORE/AFTER UPDATE
外鍵約束可能違反 FK (刪除父行)安全(不刪除行)
權限需求INSERT + DELETEINSERT + UPDATE
Binlog 記錄記錄為 DELETE + INSERT記錄為 UPDATE
適用場景全行替換、結構同步、無外鍵計數器、部分欄位更新、有外鍵

關鍵選型決策樹

  1. 需保留自增 ID / 現有行其他欄位值 → ON DUPLICATE KEY UPDATE
  2. 需基於原值運算(計數、累加、追加) → ON DUPLICATE KEY UPDATE
  3. 表有外鍵被引用 → ON DUPLICATE KEY UPDATE (REPLACE 會觸發 FK 刪除檢查)
  4. 多唯一索引且需「任一衝突即更新」語義 → ON DUPLICATE KEY UPDATE
  5. 需完全替換整行、無外鍵依賴、可接受新自增 ID → REPLACE INTO 可接受
  6. 異構同步/ETL 全量覆蓋 → REPLACE INTO 語義更直觀

進階:VALUES() 函數引用插入值

INSERT INTO `stats` (`date`, `type`, `count`) 
VALUES ('2023-10-15', 'login', 1)
ON DUPLICATE KEY UPDATE 
    `count` = `count` + VALUES(`count`);  -- 引用插入值中的 1

-- 批量 Upsert
INSERT INTO `stats` (`date`, `type`, `count`) VALUES
    ('2023-10-15', 'login', 10),
    ('2023-10-15', 'purchase', 5)
ON DUPLICATE KEY UPDATE 
    `count` = `count` + VALUES(`count`);

效能優化建議

  • 批量 Upsert 時,單次 VALUES 列表控制在 1000-5000 行,避免封包過大
  • 確保衝突鍵有索引,避免全表掃描檢測衝突
  • 高併發下考慮 INSERT IGNORE + 應用層重試,或分佈式鎖保護熱點行
  • 監控 Innodb_row_lock_waits、Innodb_deadlocks 指標

Upsert 不是銀彈。理解 REPLACE 的「毀滅性替換」與 ON DUPLICATE KEY UPDATE 的「精準修補」本質差異,結合業務語義與資料模型約束選型,才能在高併發寫入中兼顧正確性與效能。

copyright © 萬盛學電腦網 all rights reserved