您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL ON DUPLICATE KEY UPDATE 寫入去重與更新實戰詳解

編輯：MySQL 綜合教學

核心語法

INSERT INTO tbl (pk_col, col1, col2) VALUES (v1, v2, v3)
ON DUPLICATE KEY UPDATE col1 = NEW_VALUE, col2 = col2 + 1;

前提：表必須有 PRIMARY KEY 或 UNIQUE INDEX，觸發衝突時轉為更新。

行為對比

場景	SQL	效果等價於
單行衝突，增量更新	`INSERT INTO t(a,b,c) VALUES(1,2,3) ON DUPLICATE KEY UPDATE c=c+1, b=b-1;`	`UPDATE t SET c=c+1, b=b-1 WHERE a=1;`
多行批量，取 VALUES()	`INSERT INTO t(a,c) VALUES(1,3),(1,7) ON DUPLICATE KEY UPDATE c=VALUES(c);`	衝突行 c=7（第二行值），非衝突行 c=3
多行批量，增量更新	`INSERT INTO t(a,c) VALUES(1,3),(1,7) ON DUPLICATE KEY UPDATE c=c+1;`	衝突行 c=4（原值 3+1），忽略第二行值

VALUES() 函數關鍵點

僅在 ON DUPLICATE KEY UPDATE 子句中可用。
返回 INSERT ... VALUES 列表中對應列的**當前行值**；多行批量時，每行各自取自己的值。
不可在 WHERE、SELECT 列表中使用。

實戰範例：用戶最後登錄時間與次數統計

CREATE TABLE user_login_stat (
  user_id BIGINT NOT NULL PRIMARY KEY,
  login_count INT NOT NULL DEFAULT 0,
  last_login DATETIME NOT NULL,
  last_ip VARCHAR(45) NOT NULL,
  UNIQUE KEY uk_user (user_id)
);

-- 每次登錄調用
INSERT INTO user_login_stat (user_id, login_count, last_login, last_ip)
VALUES (?, 1, NOW(), ?)
ON DUPLICATE KEY UPDATE
  login_count = login_count + 1,
  last_login = VALUES(last_login),
  last_ip = VALUES(last_ip);

單條 SQL 完成「首次插入 / 非首次累加計數 + 更新時間 IP」，無需應用層 SELECT → IF → INSERT/UPDATE 往返。

進階用法：配合 LAST_INSERT_ID() 獲取自增 ID

INSERT INTO log (type, content) VALUES ('login', '...')
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID(); -- 返回插入行 ID 或衝突行 ID

適用於「唯一鍵非自增主鍵」場景，需主鍵為自增列。

性能與鎖行為

行鎖：衝突時對唯一鍵索引記錄加 X 鎖，非衝突插入加 INSERT INTENTION 鎖。
死鎖風險：高併發批量 ON DUPLICATE KEY UPDATE 多行時，不同事務鎖獲取順序不一致易死鎖 → 應用層統一按主鍵排序後批量寫入。
複製影響：Statement 格式 Binlog 記錄完整 SQL，Row 格式記錄前後映像，主從一致性無問題。

避坑清單

錯誤做法	後果	正確做法
表無 PK/UK	語法報錯 `ERROR 1138`	先加唯一索引
UPDATE 子句引用未列出的列	報錯 `Unknown column`	INSERT 列表包含所有需更新的列，或用子查詢
VALUES(col) 用在非衝突行	返回 NULL	理解：僅衝突行才取 VALUES()
作為標準 SQL 移植	PostgreSQL 用 `ON CONFLICT`，Oracle 用 `MERGE`	封裝 DAL 層適配不同方言