INSERT ... ON DUPLICATE KEY UPDATE ... 批量插入与更新(存在则更新，不存在则插入)

秒速五厘米 2023-07-11 15:28 39阅读 0赞

> 欢迎大家关注我的公众号【老周聊架构】，Java后端主流技术栈的原理、源码分析、架构以及各种互联网高并发、高性能、高可用的解决方案。

## 一、前言 ##

作为后台开发，相信大家在做业务开发时，经常会遇到这样的场景。那就是数据库没有该数据的话就做插入操作，有的话就做更新操作。

**方法1：遍历集合，判断里面的对象是否存在**

存在则单个update，不存在则单个insert。这样的逻辑看着貌似没问题，其实问题大着呢。要是数据量多的话，你哟一个个判断，一个个执行update或者insert操作，这样频繁操作数据库的话，性能影响超级大，直接会超时。所以这个方法不可取。

**方法2：取交集与取并集**

这种方法参考下面的：

[Java如何优雅的处理插入数据，数据库有相同的数据则更新，没有的话则插入][Java]

**方法3：INSERT … ON DUPLICATE KEY UPDATE …**

在并发量比较高的时候，可能两个线程都查询某个记录不存在，所以会执行两次插入，然后其中一条必然会因为主键（这里说的主键不是递增主键）冲突而失败。

数据库层MySQL中INSERT … ON DUPLICATE KEY UPDATE … 就可以做这个事情，并且是原子性操作，也是本文主要讲的。

## 二、INSERT … ON DUPLICATE KEY UPDATE … 语句 ##

**1、单条记录下使用：**

INSERT INTO t1 (a,b,c) VALUES (1,2,3) 
    	ON DUPLICATE KEY UPDATE c=c+1;

如上sql假如t1表的主键或者UNIQUE 索引是a，那么当执行上面sql时候，如果数据库里面已经存在a=1的记录则更新这条记录的c字段的值为原来值+1，然后返回值为2。如果不存在则插入a=1,b=2,c=3到数据库，然后返回值为1。

**2、多条记录下使用：**

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) 
    	ON DUPLICATE KEY UPDATE c=VALUES(c);

## 三、项目中使用 ##

**1、dao层定义接口**

void batchInsertOrUpdate(List<PoiShopEntity> shopEntityList);

**2、对应的PoiShopMapper.xml**

<insert id="batchInsertOrUpdate" parameterType="java.util.List">
        <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
            select LAST_INSERT_ID()
        </selectKey>
        INSERT INTO
        <include refid="BASE_TABLE"/>
        (
            <include refid="BASE_COLUMN" />
        )
        VALUES
        <foreach collection="shopEntityList" item="items" separator=",">
            (
            <if test="items.pid != null">
                pid = #{items.pid, jdbcType=VARCHAR},
            </if>
            <if test="items.name != null">
                name = #{items.name, jdbcType=VARCHAR},
            </if>
            <if test="items.sex != null">
                sex = #{items.sex, jdbcType=INTEGER},
            </if>
            <if test="items.age != null">
                age = #{items.age, jdbcType=VARCHAR},
            </if>
            <if test="items.address != null">
                address = #{items.address, jdbcType=INTEGER},
            </if>
            <if test="items.phone != null">
                phone = #{items.phone, jdbcType=VARCHAR}
            </if>
            )
        </foreach>
        ON DUPLICATE KEY UPDATE
            pid = VALUES(pid),
            name = VALUES(name),
            sex = VALUES(sex),
            age = VALUES(age),
            address = VALUES(address),
            phone = VALUES(phone)
    </insert>

这样就可以完美的实现了批量插入与更新操作。

不过这里我遇到了一个小问题，就是数据量大的时候数据库的一个参数`max_allowed_packet`不够大导致sql异常。

在MySQL上执行`show VARIABLES like '%max_allowed_packet%';`结果为`max_allowed_packet`为`4M`：

![在这里插入图片描述][20200306170732526.png]

**解决办法：**

重新设置参数值

1024\*1024 代表1M，我这里设置了20M，具体设置多少看你自己的业务量。

set global max_allowed_packet = 2*1024*1024*10;

再重启MySQL就可以了！！！

[Java]: https://blog.csdn.net/riemann_/article/details/104320812
[20200306170732526.png]: https://img-blog.csdnimg.cn/20200306170732526.png