萬盛學電腦網

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

MySQL ON DUPLICATE KEY UPDATE 與 REPLACE INTO:主鍵衝突時「存在更新、不存在插入」完整指南

核心語法:INSERT ... ON DUPLICATE KEY UPDATE

要求:表有 主鍵 (PRIMARY KEY) 或 唯一索引 (UNIQUE INDEX)。

INSERT INTO table (a, b, c) VALUES (1, 2, 3)
ON DUPLICATE KEY UPDATE c = c + 1;
  • 若 a=1 不存在 → 插入新行,受影響行數 = 1。
  • 若 a=1 已存在 → 更新該行 c = c + 1,受影響行數 = 2。

進階用法

1. 多行插入 + VALUES() 函數引用插入值

INSERT INTO table (a, b, c) VALUES
(1, 2, 3),
(2, 5, 7),
(3, 3, 6),
(4, 8, 2)
ON DUPLICATE KEY UPDATE b = VALUES(b);
  • 假設表已有 (2,2,9) 和 (3,2,1)。
  • 執行後:
    a=2 行更新為 (2,5,9)(b 取插入值 5)
    a=3 行更新為 (3,3,1)(b 取插入值 3)
    新增 a=1、a=4 兩行。
  • VALUES(col) 只在 ON DUPLICATE KEY UPDATE 子句中有效,其他場合返回 NULL。

2. 多唯一鍵表的行為

若表有多個 UNIQUE KEY,ON DUPLICATE KEY UPDATE 遇到任一唯一鍵衝突即觸發更新(僅更新第一條匹配行),建議避免在多唯一鍵表使用,或確保業務邏輯無歧義。

替代方案:REPLACE INTO

語法

REPLACE INTO users (id, name, age) VALUES (123, '趙本山', 50);
-- 或
REPLACE INTO users SET id = 123, name = '趙本山', age = 50;

機制

  • 本質:DELETE 舊行 + INSERT 新行(原子操作)。
  • 需表有唯一索引且不允許 NULL。
  • 返回影響行數:1=純插入,2=刪1插1,>2=多唯一鍵觸發多行刪除後插入。

多唯一鍵示例

CREATE TABLE t1 (a INT UNIQUE, b INT UNIQUE, c INT UNIQUE);
INSERT INTO t1 VALUES (1,1,1), (2,2,2), (3,3,3);
REPLACE INTO t1 (a,b,c) VALUES (1,2,3);
-- 返回 4 rows affected
-- 結果:表中僅剩 (1,2,3),原 (1,1,1)、(2,2,2)、(3,3,3) 全被刪除

對比選型

特性INSERT ... ON DUPLICATE KEY UPDATEREPLACE INTO
操作本質就地 UPDATE(保持行不變)DELETE + INSERT(行物理重建)
自增 ID不變重新生成(可能改變)
外鍵關聯安全(行不刪除)危險(觸發 CASCADE DELETE)
多唯一鍵衝突即更新第一匹配行所有衝突行全刪除再插入
性能較高(避免刪除/插入開銷)較低(雙倍寫入)
適用場景計數器累加、狀態更新、upsert 通用需完全替換行、無外鍵依賴

實戰範例:微信粉絲狀態同步

$sql = "INSERT INTO weixin_user (wx_id, wx_name, wx_state, wx_info, wx_lasttime)
        VALUES ('$wx_id', '$wx_name', '$wx_state', '$wx_info', NOW())
        ON DUPLICATE KEY UPDATE
            wx_name = VALUES(wx_name),
            wx_state = VALUES(wx_state),
            wx_info = VALUES(wx_info),
            wx_lasttime = NOW();";

優勢:單條 SQL 完成「新粉絲入庫、老粉絲更新狀態與時間」,無需應用層判斷邏輯,無競態條件。

注意事項

  • 非標準 SQL:僅 MySQL/MariaDB 支持,遷移 PostgreSQL/Oracle 需改用 MERGE / UPSERT。
  • 觸發器:ON DUPLICATE KEY UPDATE 會觸發 BEFORE UPDATE、AFTER UPDATE;REPLACE 觸發 BEFORE DELETE/AFTER DELETE + BEFORE INSERT/AFTER INSERT。
  • 複制:基於語句複製時,主備須確保唯一鍵定義一致,否則可能數據不一致。
copyright © 萬盛學電腦網 all rights reserved