mybatis批量更新及其效率问题

古城微笑少年丶 2022-12-20 03:15 623阅读 0赞

### mybatis批量更新及其效率问题 ###

*   *  [一：背景][Link 1]
     *   *  [注意：][Link 2]
         *   *  [第一种：][Link 3]
             *  [第二种:][Link 4]
     *  [二：批量更新的方式总结：][Link 5]
     *   *  [第一种：][Link 6]
         *  [第二种：][Link 7]
     *  [三：总结：][Link 8]

最近，负责公司一些旧数据的批量整理和清洗工作，在网上寻找了两种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的批量操作处理有了一个大概轮廓了，不再神秘，所以，我们学习技术不仅仅要知其然，更要知其所以然。

[Link 1]: https://blog.csdn.net/qq_40837310/article/details/92385458?utm_medium=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase&depth_1-utm_source=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase#_4
[Link 2]: https://blog.csdn.net/qq_40837310/article/details/92385458?utm_medium=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase&depth_1-utm_source=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase#_6
[Link 3]: https://blog.csdn.net/qq_40837310/article/details/92385458?utm_medium=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase&depth_1-utm_source=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase#_22
[Link 4]: https://blog.csdn.net/qq_40837310/article/details/92385458?utm_medium=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase&depth_1-utm_source=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase#_25
[Link 5]: https://blog.csdn.net/qq_40837310/article/details/92385458?utm_medium=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase&depth_1-utm_source=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase#_37
[Link 6]: https://blog.csdn.net/qq_40837310/article/details/92385458?utm_medium=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase&depth_1-utm_source=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase#_38
[Link 7]: https://blog.csdn.net/qq_40837310/article/details/92385458?utm_medium=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase&depth_1-utm_source=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase#_56
[Link 8]: https://blog.csdn.net/qq_40837310/article/details/92385458?utm_medium=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase&depth_1-utm_source=distribute.wap_relevant_bbs_down.none-task-blog-baidujs-2.nonecase#_86