MySQL批量更新的常用实践

MySQL批量更新的常用方法

批量更新一般在批处理系统或者定时任务中比较常见，常见的诉求就是对表中多条数据进行更新（待更新的值是不一样的，这个区别于update ... where in(...)）

1.利用case ... when ... 方式批量更新

特点：适合数据量小的更新，数据量大时可能会产生间隙锁，甚至表锁，会影响性能，这个需要留意

常见的sql脚本如下：

UPDATE t_demo_audit_order SET
 prod_no = CASE id WHEN 1 THEN 'C1' WHEN 2 THEN 'C2' WHEN 3 THEN 'C3' WHEN 4 THEN 'C4' END,
 busi_no = CASE id WHEN 1 THEN 'B1' WHEN 2 THEN 'B2' WHEN 3 THEN 'B3' WHEN 4 THEN 'B4' END
WHERE id IN (1, 2, 3, 4)

mybatis动态拼接的sql脚本如下：

<update id="batchUpdateByPrimaryKey" parameterType="java.util.List">
        update t_demo_audit_order set
        prod_no = case id
        <foreach collection="list" item="item">
            when #{item.id} then #{item.prodNo}
        </foreach>
        end,
        busi_no = case id
        <foreach collection="list" item="item">
            when #{item.id} then #{item.busiNo}
        </foreach>
        end
        where id in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item.id}
        </foreach>
    </update>

2.批量执行单条update语句

特点：可以充分利用索引，有较好的性能；

注意：sql的大小不能超过数据库的限制，否则会失败。一个批次最大可以执行多少条update语句，这个没有绝对的数据，需要依据自己的表结构及数据量在测试环境进行
尝试，找到最佳数量。比如你可以依次执行1000条，2000条，3000条，然后对比性能即可选出理想的数量

ps: 数据库连接配置需要增加参数 allowMultiQueries=true

db连接配置url.png

sql脚本如下：

update demo_record set test_order_no = 'bar01', test_dt = 'xxx' where id = 1 ; 
update demo_record set test_order_no = 'bar02', test_dt = 'xxx' where id = 2 ;
update demo_record set test_order_no = 'bar03', test_dt = 'xxx' where id = 3 ;
update demo_record set test_order_no = 'bar04', test_dt = 'xxx' where id = 4 ;
update demo_record set test_order_no = 'bar05', test_dt = 'xxx' where id = 5 ;
update demo_record set test_order_no = 'bar05', test_dt = 'xxx' where id = 6 ;
......

mybatis动态拼接的sql脚本如下：

<update id="batchUpdateByPrimaryKey"  parameterType="java.util.List">
    <foreach collection="list" item="item" open="" close="" separator=";">
        update demo_record set
        test_order_no = #{item.testOrderNo},
        test_dt = #{item.testDt}
        where id = #{item.id}
    </foreach>
</update>

之前处理的一个需求，满足自己的业务需求即可，sql耗时如下：

批量更新耗时.png