MySQL批量更新的常用方法

批量更新一般在批處理系統(tǒng)或者定時任務中比較常見，常見的訴求就是對表中多條數(shù)據(jù)進行更新（待更新的值是不一樣的，這個區(qū)別于update ... where in(...)）

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

特點：適合數(shù)據(jù)量小的更新，數(shù)據(jù)量大時可能會產生間隙鎖，甚至表鎖，會影響性能，這個需要留意

常見的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動態(tài)拼接的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.批量執(zhí)行單條update語句

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

注意：sql的大小不能超過數(shù)據(jù)庫的限制，否則會失敗。一個批次最大可以執(zhí)行多少條update語句，這個沒有絕對的數(shù)據(jù)，需要依據(jù)自己的表結構及數(shù)據(jù)量在測試環(huán)境進行
嘗試，找到最佳數(shù)量。比如你可以依次執(zhí)行1000條，2000條，3000條，然后對比性能即可選出理想的數(shù)量

ps: 數(shù)據(jù)庫連接配置需要增加參數(shù) 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動態(tài)拼接的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>

之前處理的一個需求，滿足自己的業(yè)務需求即可，sql耗時如下：

批量更新耗時.png