mysql批量更新的两种方式效率试验<二>

时间:2022-07-16 20:38:23

Mysql两种批量更新的对比

简介:

mysql搭载mybits框架批量更新有两种方式,一种是在xml中循环整个update语句,中间以‘;’隔开,还有一种是使用case when 变相实现批量更新,现就两种方法的效率做以对比。

两种写法:

方法一:整体循环update语句

注意:mysql默认不支持一条预编译sql以 ’;  ’ 分割这种方式执行,需要在mysql 的jdbcUrl参数上追加 allowMultiQueries=true开启支持

SQL如下:

<!-- 批量更新表整体循环update语句-->
<update id="updateByForech" parameterType="java.util.List">
  <foreach collection="list" item="item" >
    update t_user
    <set>
      <if test="item.tName != null and item.tName != ''">
        t_name = #{item.tName,jdbcType=VARCHAR},
      </if>
      <if test="item.tAge != null">
        t_age = #{item.tAge},
      </if>
      <if test="item.tAddress != null">
        t_address = #{item.tAddress,jdbcType=VARCHAR},
      </if>
      <if test="item.tPwd!= null">
        t_pwd = #{item.tPwd,jdbcType=VARCHAR},
      </if>
    </set>
    where t_id = #{item.tId};
  </foreach>
</update>

该方法编写简单,便于理解,但是需要修改系统参数。

方法二:使用case when 方式拼接

SQL如下:

  <!--caseWhen更新-->
 
<update id="updateByCaseWhen" parameterType="java.util.List">
   update t_user
  <trim prefix="set" suffixOverrides=",">
    <trim prefix="t_name =case" suffix="end,">
      <foreach collection="list" item="item">
        <if test="item.tName !=null and item.tName != ''">
          when t_id=#{item.tId} then #{item.tName,jdbcType=VARCHAR}
        </if>
      </foreach>
    </trim>
    <trim prefix="t_address =case" suffix="end,">
      <foreach collection="list" item="item">
        <if test="item.tAddress != null and item.tAddress != ''">
          when t_id=#{item.tId} then #{item.tAddress,jdbcType=VARCHAR}
        </if>
      </foreach>
    </trim>
    <trim prefix="t_age =case" suffix="end,">
      <foreach collection="list" item="item">
        <if test="item.tAge != null">
          when t_id=#{item.tId} then #{item.tAge}
        </if>
      </foreach>
    </trim>
    <trim prefix="t_pwd =case" suffix="end,">
      <foreach collection="list" item="item">
        <if test="item.tPwd != null">
          when t_id=#{item.tId} then #{item.tPwd}
        </if>
      </foreach>
    </trim>
  </trim>
  where t_id in
  <foreach collection="list" item="item" separator="," open="(" close=")">
    #{item.tId}
  </foreach>
</update>

该方法sql 拼接复杂,为每一个要更新的字段罗列出id值,若数据出现问题,定位困难。

效率统计:

  1. 批量更新10条数据两种方法用时统计:

时间可以忽略不计;

  1. 批量更新100条数据两种方法用时统计:
  1. 批量更新1000条数据两种方法用时统计:
  1. 批量更新5000条数据两种方法用时统计:
  1. 批量更新10000条数据两种方法用时统计:

总结:

1.一次修改数据量较小的情况下, case when方式 和 整体更新方式 均可选择,但是整体更新需要新增系统参数配置

2.对于经常出现数据问题的,建议使用整体循环update方式,该方式生sql就是简单的update语句的拼接,便于定位问题。而case when方式 会在每一个需要修改的参数后面罗列出主键,切在mapper文件中出现大量冗余。

3.对于批量更新数据量很大的情况,case when效率低下,建议使用整体循环update。

该项目源码地址:https://git.lug.ustc.edu.cn/zhaiyt/demo7.git