mybatis批量更新及其效率问题

• • 一：背景
  • • 注意：
    • • 第一种：
      • 第二种:
  • 二：批量更新的方式总结：
  • • 第一种：
    • 第二种：
  • 三：总结：

最近，负责公司一些旧数据的批量整理和清洗工作，在网上寻找了两种mybatis批量更新的方法。现在在这里总结下和说明下遇到的问题。

一：背景

公司旧数据的清洗，比如图片路径的改变，日期格式的改变（日期格式是varchar），因为数据大体上有一定的规律可寻，所以我的解决思路是用mybatis操作数据库，把需要清洗的数据查询出来并按一定规律进行清洗，在批量更新进数据库中。在这里尝试了两种方式的批量更新。

注意：

public void updateData(List<Map<String, Object>> map);

• 1.这条批量更新的传入参数是一个List<Map<String, Object>> map。

url: jdbc:mysql://localhost:3306/task?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&allowMultiQueries=true

• 2.jdbc与mysql的连接的url中要添加allowMultiQueries=true参数。本人因为刚开始没有加这个参数，导致一直报错。

### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (12,981,868 > 4,194,304)

• 这是因为mysql数据库限制了处理文件的大小，默认是4MB，修改即可。

修改方法：

第一种：

在mysql的配置文件my.ini中添加 max_allowed_packet =67108864 ，我这里设置的是64MB，各位可以按照需要自行设置，这种方法是修改配置文件，所以就算数据库重启也会生效。

第二种:

set global max_allowed_packet = 64*1024*1024; 

• 执行此sql语句可以把文件处理的最大值设置为64MB，需要多少自行决定，这种方式修改，数据库重启之后会重置为默认值。

show VARIABLES like '%max_allowed_packet%';

• 这个sql语句是查询文件处理的最大值是多少。

二：批量更新的方式总结：

第一种：

    <update id="updata1">
		<foreach collection="list" item="item" separator=";">
			update refund_assistant_stqd set images=#{item.images} where id=#{item.id}
		</foreach>
	</update>

种方法会生成这样的语句：

update refund_assistant_stqd set images=#{item.images} where id=#{item.id};
update refund_assistant_stqd set images=#{item.images} where id=#{item.id};
......

样与在java代码里面循环做一个循环没有啥本质上区别，根本就不是批量操作。也使用过了。效率极其之低，并且对数据库的负载相当之大，运行期间磁盘IO达到了百分之百，这种方法是不可取的，望后面的人能警醒。

第二种：

<update id="updateData">
		  update refund_assistant_stqd
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="images =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                     when id=#{item.id} then #{item.images}
                </foreach>
            </trim>
        </trim>
        where id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.id}
        </foreach>
	</update>

种方法会生成这样的语句：

update refund_assistant_stqd set 
images= (case when id=##{item.id} then #{item.images}),
images= (case when id=##{item.id} then #{item.images}),
.....
where id in (#{item.id},#{item.id},...)

种方法采用了case when 机制，使得相应的id与images能相匹配，只生成了一条sql语句，所以对数据库的压力会大大的缩小，时间都会花费在sql字符串的拼接上，并且sql的拼接是在你的电脑上完成的，不会对远程数据库的服务器产生不必要的负载。效率提高了很多。本人更新了12万条数据用了367秒，上面的第一种方法运行了20分钟之后我就放弃了，所以并没有测试出时间。

三：总结：

解决完以上背景里面的注意事项之后，本人的任务就执行完成了。
在这里要说一下，mybatis的批量操作原理就是使用sql 字符串的拼接技术，了解其原理之后对mybatis的批量操作处理有了一个大概轮廓了，不再神秘，所以，我们学习技术不仅仅要知其然，更要知其所以然。