MySQL的on duplicate key update实现(批量)插入或更新操作

---

一. 背景

1. 背景：业务上经常有这样的需求场景，如果之前有这条数据，就做更新；如果没有，就做新增。
2. 常用的处理方案：通过主键id或者其他唯一键判断DB中是否有这条数据，再判断调用insert或update语句。这样做逻辑处理起来比较复杂，降低代码效率，而且如果并发量高，可能会存在数据问题。

二. on duplicate key update概述

1. 为了应对这种业务场景，MySQL有一种专有语法（insert into ... on duplicate key update），一条SQL语句实现插入或更新，可单条可批量。
2. 使用要点
   1. 表要求必须有主键或唯一索引才能起效果，否则insert或update无效。
   2. 该语法是根据主键或唯一键来判断是新增还是更新
   3. VALUES() 后面应为需要更新的字段，不需要更新的字段不用罗列；
   4. 遇到已存在记录（根据唯一键或主键）时，自动更新已有的数据；如果表中有多个唯一键（可以是单列索引或复合索引），则任意一个唯一键冲突时，都会自动更新数据。
   5. 所有操作均由SQL处理，不需要额外程序代码分析，能够大幅提高程序执行效率。
3. on duplicate key update&&replace into
   1. 相较于replace into（insert加强版，不存在时insert，存在时先delete后insert）虽然也能达到批量更新目的，但因为删除和添加需要重复维护索引，所以大批量比on duplicate key update性能要差，小量可忽略，需使用者自行选择。
   2. replace into 遇到已存在的记录，会先删除掉表中原有的记录后，再插入新的记录，这样会导致该记录的主键发生变化，如果该表的主键和其它表有业务关联，那么会导致关联数据丢失。
   3. replace into 插入的主键在某些时候不是连续自增的，这样会导致主键增长的数据很快，有时候会超过 int(10) 的最大值

---

三. on duplicate key update的使用

1. 在MySQL中的使用

1. 创建表

create tableA (
    name varchar(50) DEFAULT null, 
    type TINYINT DEFAULT NULL,
	a varchar(50) NOT NULL,
    b varchar(50) NOT NULL,
    c varchar(50) NOT NULL,
    UNIQUE KEY (a,b,c)
)

2. 现有数据

name	type	a	b	c
哈哈	1	5	1	2
哈	5	1	2	4

3. MySQL的SQL插入或更新数据

-- 第一条插入；第二条更新
INSERT INTO tableA(name, type, a, b, c) values('啊喔额',11,1,2,3),('阿哈湖',6, 1,2,4) on DUPLICATE KEY UPDATE name=values(name), type =values(type)

4. 执行SQL后的结果

name	type	a	b	c
哈哈	1	5	1	2
阿哈湖	6	1	2	4
啊喔额	11	1	2	3

---

2. 在MyBatis中的使用

1. 单条插入或更新

// Dao层
int saveOrUpdate(ADTO dto);

<!-- Mapper -->
<insert id="saveOrUpdate" >
    insert into tableA(name,type,a,b,c) values (#{name}, #{type}, #{a}, #{b}, #{c})
    ON DUPLICATE KEY UPDATE name=values(name),type=values(type)    
</insert>

2. 批量插入或更新

// Dao层
int batchSaveOrUpdate(@Param("list") List<ADTO> list);

<!-- Mapper -->
<insert id="batchSaveOrUpdateProduct" parameterType="java.util.List">
    insert into tableA(name,type,a,b,c) values
    <foreach collection="list" item="item" separator=",">
        (#{item.name},#{item.type},#{item.a},#{item.b},#{item.c})
    </foreach>
    on DUPLICATE KEY UPDATE name=values(name),type=values(type)
</insert>

注意：values()里面要用数据库字段来实现对数据的更新，而不是传入的参数字段

---

参考资料

• Mybatis：通过on duplicate key update实现批量插入或更新_琦彦-程序员宅基地
• on duplicate key update 批量更新操作
• Mybatis 批量插入操作ON DUPLICATE KEY UPDATE使用
• MySQL on duplicate key update 批量插入并更新已存在数据