数据库批量Update的几种方式

1. 代码层面for循环中一条条更新

for(PartyInfo partyInfo: partyInfoList) {
   partyMapper.update(partyInfo);
}

这种方法相对来说是见得最多的，但是他的缺点是每次更新都要请求到数据库，网络会有一定的开销

2. foreach 拼接 update 语句后批量更新（推荐）

<update id="updateForBatch" parameterType="com.grazy.entity.PartyInfo">
    <foreach collection="partyInfoList" item="entity" separator=";">
        UPDATE tb_part
        SET party_name = #{entity.partyName},create_user_id=#{entity.createUserId},
            create_user_name=#{entity.ceateUserName}
        <where>
            id = #{entity.id}
        </where>
    </foreach>
</update>

这种方式可能见的比较少，这种与第一种批量存在的区别是，这种是批量一次性请求数据库。

使用foreach做批量更新的时候，一次性更新的数据不宜太多，尽量控制在1000以内，这样更新的性能还是不错的。

如果需要更新的数据超过了1000，则需要分成多批更新。

3. 使用 case when 的方式做判断

<update id="updateForBatch" parameterType="com.grazy.entity.PartyInfo">
    UPDATE tb_part
    <trim prefix="SET" suffixOverrides=",">
        <trim prefix="party_name = CASE id" suffix="END,">
            <foreach collection="partyInfoList" item="entity">
                WHEN #{entity.id} THEN #{entity.partyName}
            </foreach>
        </trim>
        <trim prefix="create_user_id = CASE id" suffix="END,">
            <foreach collection="partyInfoList" item="entity">
                WHEN #{entity.id} THEN #{entity.createUserId}
            </foreach>
        </trim>
        <trim prefix="create_user_name = CASE id" suffix="END">
            <foreach collection="partyInfoList" item="entity">
                WHEN #{entity.id} THEN #{entity.createUserName}
            </foreach>
        </trim>
    </trim>
    <where>
        id IN (
        <foreach collection="partyInfoList" item="entity" separator=",">
            #{entity.id}
        </foreach>
        )
    </where>
</update>

这种方式写法相对比较复杂，在随着数据量上来后，效率就会下降的很快，当数据小于1000时，速度还稍微比一条条批量更新快些，但是到1万左右差距就上来了，落后一条条批量更新很多，数据量越多落后越多。

4. 使用 ON DUPLICATE KEY UPDATE

 <update id="updateForBatch" parameterType="com.grazy.entity.PartyInfo">
    insert into tb_part
    (create_user_id,party_name,create_user_name) values
    <foreach collection="list" index="index" item="item" separator=",">
        (#{item.createUserId},
        #{item.partyName},
        #{item.createUserName})
    </foreach>
    ON DUPLICATE KEY UPDATE
     party_name = values(partyName)
</update>

在插入数据时，数据库会先判断数据是否存在，如果不存在，则执行插入操作。如果存在，则执行更新操作。需要存在一个唯一索引字段来校验是否重复插入，触发修改操作

这种方式批量更新数据，性能比较好，但很少会用，因为非常容易出现死锁的问题。

在使用 foreach 中出现的问题

问题：sql injection violation, multi-statement not allow

原因：阿里巴巴druid包的WallFilter中报出来的，它里面有个checkInternal方法，会对sql语句做一些校验，如果不满足条件，就会抛 异常。druid默认不支持一条sql语句中包含多个statement语句

解决方案：

MySQL默认也是关闭批量更新数据的，不过我们可以在jdbc的url要上，添加字符串参数：&allowMultiQueries=true，开启批量更新操作。

datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/grazy_date?useUnicode=true&characterEncoding=utf-8
           &useSSL=false&allowMultiQueries=true
      username: root
      password: 123456

如果使用的是druid数据库驱动，那么需要在配置文件中调整 filter 的参数

spring:
  datasource:
    druid:
      filter:
        wall:
          config:
            multi-statement-allow: true
            none-base-statement-allow: true

如果使用的其他数据库驱动，例如：baomidou。这时候，需要查看它的源码，确认它multi-statement-allow的配置参数是怎么配置的，有可能跟druid不一样。

学习来源：苏三说技术