mybatis数据批量更新

mysql8
jdk8
mybatis
经常遇到的场景就是需要进行批量更新，更新同一张表中不同字段。这样常见的单字段批量更新就不行了。

列举常见的批量更新方式

1. 代码中foreach update
2. 多线程foreach update
3. mybatis xml中foreach 多条sql拼接
4. mybatis xml中case when
5. mybatis-plus方式
6. 业务控制-先批量删除再批量新增代替更新
7. ON DUPLICATE KEY UPDATE
8. REPLACE INTO

方式1 和 方式2

基本只有初学者会使用。
因为重复的IO连接与断开效率极低，导致性能很差。

mybatis xml中foreach 多条sql拼接

mybatis xml中foreach

前置条件：url连接配置必须设置allowMultiQueries=true

<update id="batchUpdate" parameterType="com.TestDO">
    <foreach collection="offerList" item="offer" separator=";">
        update offer
        set
        <if test="offer.name!=null and offer.name!=''">
            name=#{offer.name},
        </if>
        update_time=#{offer.updateTime}
        where
        id = #{offer.id}
    </foreach>
</update>

该方式采用mybatis动态拼接方式，多个update语句使用;隔开，多个语句一次性提交给mysql，达到批量执行。类似于执行sql脚本方式。

allowMultiQueries=true 参数的作用：

• 可以在sql语句后携带分号，实现多语句执行。
• 可以执行批处理，同时发出多个SQL语句。

mybatis xml中case when

<update id="updateBatch">
	update offer
        <trim prefix="set" suffixOverrides=",">
            <trim prefix=" `name` = case " suffix=" end, ">
                <foreach collection="list" item="item">
                    <if test="item.name!= null">
                        when `id` = #{item.id} then #{item.name}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        `id` in
        <foreach collection="list" item="item" open="(" close=")" separator=",">
            #{item.id}
        </foreach>
</update>

效果

UPDATE offer SET name=
CASE
	WHEN id = 1 THEN '测试商品1' 
	WHEN id = 2 THEN '测试商品2' 
END 
WHERE id IN ( 1, 2 );

优点：一条sql搞定，比方式一和方式二要快，只与mysql交互了一次。不需要额外配置参数支持。

弊端：如果条件过多，case when 需要判断多次，mysql处理效率会随着条件数量增多而持续下降。
如截图所示

由于是动态拼接，一定要注意条件的写法，多多测试，查看拼接的sql。

mybatis-plus 方式

com.baomidou:mybatis-plus:3.3.0

前置条件：url连接配置必须设置allowMultiQueries=true
rewriteBatchedStatements=true

com.baomidou.mybatisplus.extension.service.IService中有封装方法

源码中可以看到是基于主键id去更新的。

将多个update语句，分批次提交到数据库，默认是1000条。这里的更新方式与 mybatis xml中foreach 多条sql拼接 类似，都是一次性传递多条语句给数据库。
//提交到数据库，保存数据 sqlSession.flushStatements();

非UPDATE_BY_ID更新

基于主键id方式更新多有不便，更多的时候，是要基于where条件去进行更新。
于是需要改造一下，在mapper文件中调用批量更新的方法中重写executeBatch方法
简单举例部分代码如下所示

Assert.notEmpty(entityList, "error: entityList must not be empty");
        String sqlStatement = sqlStatement(SqlMethod.UPDATE);
        int size = entityList.size();
        executeBatch(sqlSession -> {
            int i = 1;
            for (T entity : entityList) {
                MapperMethod.ParamMap<Object> param = new MapperMethod.ParamMap<>();
                param.put(Constants.WRAPPER, getQueryWrapper(entity));
                param.put(Constants.ENTITY, entity);
                sqlSession.update(sqlStatement, param);
                if ((i % batchSize == 0) || i == size) {
                    sqlSession.flushStatements();
                }
                i++;
            }
        });
        return true;

1、需要修改执行方式，改为UPDATE

sqlStatement(SqlMethod.UPDATE)
2、指定更新条件
param.put(Constants.WRAPPER, getQueryWrapper(entity));

private LambdaUpdateWrapper<NoticePartner> getQueryWrapper(T entity ) {
        final LambdaUpdateWrapper<entity > updateWrapper =  new UpdateWrapper<entity >().lambda();
        ......
        return updateWrapper;
    }

这样就通过WRAPPER条件，灵活多变的生成update语句。

业务控制-先批量删除再批量新增代替更新

这个没得说，先把原来数据全部删除， 在批量新增新的修改数据，控制在同一个事务内，防止数据不一致。有点类似REPLACE INTO。

ON DUPLICATE KEY UPDATE

利用数据库的特性，如果已存在该唯一标示或主键就更新，如果不存在该唯一标示或主键则作为新行插入。

<update id="updateBatch">
	insert into t_output_calendar (index, 
	  cal_date, user_type, create_time, 
	  modify_time, delete_flag
	  )
	values
	<foreach collection="list" item="item" index="index"
		separator=",">
		(
		#{item.index,jdbcType=INTEGER}, 
		#{item.calDate,jdbcType=TIMESTAMP}, 
		#{item.type,jdbcType=TINYINT}, 
		#{item.createTime,jdbcType=TIMESTAMP}, 
		#{item.modifyTime,jdbcType=TIMESTAMP}, 
		#{item.deleteFlag,jdbcType=TINYINT}
		)
	</foreach>
	ON DUPLICATE KEY UPDATE modify_time = VALUES(modify_time), user_type = VALUES(user_type);
</update>

REPLACE INTO

利用数据库的特性。
msql的replace into跟insert into的用法完全一样，但是它带有更新功能：
如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。否则，直接插入新数据。
注意，它是先删除数据，然后再插入，这是和ON DUPLICATE KEY UPDATE不同的地方,如果当前的数据库用户没有删除权限，是不能使用replace into的。
replace into 代替insert into

<insert id="updateBatch" parameterType="java.util.List">
	replace into t_output_calendar (index, cal_date, user_type, create_time, 
	  modify_time, delete_flag
	  )
	values
	<foreach collection="list" item="item" index="index"
		separator=",">
		(
		#{item.index,jdbcType=INTEGER}, 
		#{item.calDate,jdbcType=TIMESTAMP}, 
		#{item.type,jdbcType=TINYINT}, 
		#{item.createTime,jdbcType=TIMESTAMP}, 
		#{item.modifyTime,jdbcType=TIMESTAMP}, 
		#{item.deleteFlag,jdbcType=TINYINT}
		)
	</foreach>
</insert>

总结

1、方式1与方式2，为什么慢？
慢在事务和网络io上面，由于默认事务是自动提交。那么在mybatis执行update的时候，每次执行update都是全新的事务，每次都要重新连接创建事务，提交事务，事务提交就要写磁盘，导致效率慢。多次写和一次写是不一样的。

所以才衍生出多条update拼接到一起，一次性发送到数据库，让它们在同一个事务中执行。
mybatis xml中foreach 多条sql拼接、mybatis-plus 方式 都是采用这种方式。

拼接的语句要控制好数量,防止缓冲区溢出。

在MySQL中，未提交的数据实际上存储在内存中的缓冲区中。当我们向数据库发送一个新的数据修改请求时，MySQL会将这些修改暂时存储在内存中，这些修改被称为“未提交的数据”。

如果我们使用了事务，则在提交事务之前，所有的修改都不会被写入到磁盘上的数据文件中，而是存储在内存中的缓冲区中。如果在事务提交之前，数据库崩溃或者出现其它问题导致MySQL停止运行，那么这些未提交的数据将会丢失。

2、多条拼接update语句与case when效率之争

• 多条拼接update语句本质上还是多条sql，需要数据库需要执行多次，只不过是一次传给数据库的，且在同一个事务中，减少网络io事务的开销，相当于给数据库传了一个sql脚本。
• case when是一条sql，数据库执行一遍，数据量允许的情况下第二种比第一种更加的优秀。但是数据量特别大而且条件复杂多变的时候，会给数据库带来压力，效率反而很差。

你会选哪种呢？