在实际业务场景中，存在则更新，不存在则插入的场景很常见，如日统计、年统计、金额累计等，通常的做法是先查询，再根据查询结果选择插入还是更新，今天介绍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);
注意事项
1. 必须有唯一键：
- 表必须定义了主键或唯一键，否则这个语法无效
- 冲突检测依赖于这些键
2. 更新多列：
- 可以同时更新多个列，如您的例子中更新了num和unreconciled_amount
a. VALUES()函数：
- 使用VALUES(col_name)可以引用原本要插入的值
- 这在批量操作中特别有用
这种语法实现了"如果记录存在则更新，不存在则插入"的功能，常用于：
- 计数器累加（如本例中的 num 和 unreconciled_amount）
- 避免先查询再决定插入或更新的两步操作
- 保证操作的原子性
这种设计使得"存在则更新，不存在则插入"的操作变得非常简洁高效，且能避免并发问题。
