Mysql批量更新数据

谁借莪１个温暖的怀抱￠ 2022-06-10 07:44 322阅读 0赞

方式一：使用for循环，进行一条条进行更新，但是这样更新数据的方式性能很差，也容易造成阻塞。

由于这种方式的缺点，很多人都会想到，用一条sql语句实现批量更新。

方式二：使用case when方式来实现批量更新

UPDATE mytable
        SET myfield = CASE id
            WHEN 1 THEN 'value'
            WHEN 2 THEN 'value'
            WHEN 3 THEN 'value'
        END
    WHERE id IN (1,2,3)

这种方式，更新数据库，将业务逻辑放在sql中，容易导致死锁。

demo：

<update id="updateByBatchCaseWhen" parameterType="java.util.List">
    		update T_ORDER
    		<trim prefix="set" suffixOverrides=",">
    			
    			<trim prefix="IO_TYPE = case" suffix="end,">
    				<foreach collection="list" item="i" index="index">
    					<if test="i.ioType!= null">
    						when ref=#{i.ref} then #{i.ioType}
    					</if>
    				</foreach>
    			</trim>
    			
    		</trim>
    		where
    		<foreach collection="list" separator="or" item="i" index="index">
    			REF=#{i.ref}
    		</foreach>
    	</update>

方式三：replace into

replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');

操作本质：对重复的记录先delete，后insert，如果更新的字段不全会将缺失的字段置为缺省值。

demo:

<update id="updateByBatchReplace" parameterType="java.util.List">
    		replace into T_ORDER(ID,REF,IO_TYPE) values 
    		<foreach collection="list" separator="," item="i" index="index">
    			(#{i.id},#{i.ref},#{i.ioType})
    		</foreach>
    	</update>

方式四：insert into ...on duplicate key update

insert into test_tbl (id,dr) values  (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);

操作本质：只update重复记录，不会改变其他字段

这种方式，要求有一个字段必须是主键或者唯一索引。

demo:

<update id="updateByBatchDuplicate" parameterType="java.util.List">
    		insert into T_ORDER (ID,REF,IO_TYPE) values 
    		<foreach collection="list" separator="," item="i" index="index">
    			(#{i.id},#{i.ref},#{i.ioType})
    		</foreach>
    		on duplicate key update 
    		IO_TYPE=VALUES(IO_TYPE)
    	</update>

方式五：创建临时表，先更新临时表，然后从临时表中update

create temporary table tmp(id int(4) primary key,dr varchar(50));
    insert into tmp values  (0,'gone'), (1,'xx'),...(m,'yy');
    update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

创建临时表，太损耗性能，一般很少用。

总结：

性能测试结果图：

方式一测试结果图：单条记录更新

![Center][]

方式二测试结果图：case when

![Center 1][]

方式三测试结果图：replace into

![Center 2][]

方式四测试结果图：duplicate

![Center 3][]

经小编测试，前四种方式，性能最好的，要数方式四：insert into ...on duplicate key update。

[Center]: /images/20220610/6df3fc3858344e03bac52c25a78ea6b5.png
[Center 1]: /images/20220610/b516a0a36041446bbac68fe7951193e9.png
[Center 2]: /images/20220610/d3c71127f0834a76a998c34da1fe8b54.png
[Center 3]: /images/20220610/f7e95b2f1539495ba907b06a6d1d3099.png