当前位置：首页 > news >正文

如何简洁高效的实现存在则更新，不存在则插入

news 2025/7/22 7:01:50

在实际业务场景中，存在则更新，不存在则插入的场景很常见，如日统计、年统计、金额累计等，通常的做法是先查询，再根据查询结果选择插入还是更新，今天介绍MySql中的一种特殊语法，ON DUPLICATE KEY UPDATE

基本结构

INSERT INTO supplier_bill_account (id, platform_id, sjid, account_type, supplier_id, supplier_type, expect_settle_amount, settle_amount, un_settle_amount, num, unreconciled_amount)
VALUES (#{id},#{platformId},#{sjid},#{accountType},#{supplierId},#{supplierType},#{expectSettleAmount},#{settleAmount},#{unSettleAmount},#{num},#{unreconciledAmount})
ON DUPLICATE KEY UPDATE num=num+VALUES(num),unreconciled_amount=unreconciled_amount+VALUES(unreconciled_amount)

ON DUPLICATE KEY UPDATE 的作用

1. 基本功能：

当尝试插入的数据与表中现有记录的主键或唯一键冲突时（即出现重复键错误）
不执行 INSERT 操作，而是执行 UPDATE 操作，更新指定的列

2. 在这个具体语句中的行为：

如果表中已存在具有相同唯一键（可能是 id 或其他定义为唯一键的列组合）的记录
则不会插入新记录，而是更新现有记录的：

num 字段：现有值加上新值 (num=num+VALUES(num))
unreconciled_amount 字段：现有值加上新值 (unreconciled_amount=unreconciled_amount+VALUES(unreconciled_amount))

3. VALUES() 函数：

VALUES(num) 引用的是 INSERT 语句中原本要插入的 num 值
VALUES(unreconciled_amount) 同理

在 ON DUPLICATE KEY UPDATE 语句中，不需要显式指定 WHERE 条件，这是因为它有隐式的更新条件

隐式更新条件

1. 自动识别冲突键：

更新操作会自动针对导致重复键冲突的那条记录进行
冲突的判断是基于表的主键(Primary Key)或唯一键(Unique Key)

在上述的例子中：

INSERT INTO supplier_bill_account (id, platform_id, sjid, ...)
VALUES (#{id}, #{platformId}, #{sjid}, ...)
ON DUPLICATE KEY UPDATE ...

当插入的 id 值(或其它被定义为唯一键的列组合)已存在时
系统会自动找到这条已有记录进行更新
不需要手动写 WHERE id=#{id} 之类的条件

2. 为什么不需要显式条件

a. 设计原理：

这个语法本身就是为解决"插入或更新"场景设计的
数据库引擎已经知道是哪条记录导致了冲突

b. 性能优势：

避免了先查询再更新的两次操作
保证操作的原子性(不会有并发问题)

3. 与普通UPDATE的区别：

-- 普通UPDATE需要明确条件
UPDATE table SET col=val WHERE id=123;-- ON DUPLICATE KEY UPDATE不需要
INSERT INTO table(id, col) VALUES(123, val)
ON DUPLICATE KEY UPDATE col=VALUES(col);