1,单条
UPDATE ho_house
SET cn_name = CASE id
WHEN 16 THEN 7.6
WHEN 19 THEN 8.86
WHEN 20 THEN 9
END
WHERE id IN (16,19,20)
2,多条
UPDATE ho_house
SET cn_name = CASE cid
WHEN 1 THEN 'Name 1'
WHEN 2 THEN 'Name 1'
WHEN 3 THEN 'Name 1'
END,
en_name = CASE cid
WHEN 1 THEN 'Name 1'
WHEN 2 THEN 'Name 2'
WHEN 3 THEN 'Name 3'
END
WHERE cid IN (1,2,3) and pcate in (24,27,26) and ccate in (277,271,207)
...[多条件时直接: and 字段 in()]
3,mybatis 的多条 5000条用时 2分钟
<!-- 高效率批量更新sql -->
<update id="updateList" parameterType="java.util.List">
UPDATE crm_online_form_detail SET
field1 = CASE id
<foreach collection="list" item="item">
WHEN #{item.id} THEN #{item.field1}
</foreach>
END ,
field2 = CASE id
<foreach collection="list" item="item">
WHEN #{item.id} THEN #{item.field2}
</foreach>
END ,
field3 = CASE id
<foreach collection="list" item="item">
WHEN #{item.id} THEN #{item.field3}
</foreach>
END ,
field4 = CASE id
<foreach collection="list" item="item">
WHEN #{item.id} THEN #{item.field4}
</foreach>
END
WHERE id IN
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item.id}
</foreach>
</update>
4,对比 另一种批量,实际还是生成单个sql一条一条更新的 5000条用时20分钟
<!-- 批量更新sql -->
<update id="updateList" parameterType="java.util.List">
<foreach collection="list" index="index" item="e" >
UPDATE ho_house SET
code = #{e.code},
cn_name = #{e.cnName},
en_name = #{e.enName},
actual_name = #{e.actualName},
update_date = #{e.updateDate},
update_by = #{e.updateBy.id}
WHERE id = #{e.id} ;
</foreach>
</update>
5,创建临时表,先更新临时表,然后从临时表中update
create tem 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;
参考 : https://blog.csdn.net/zl570932980/article/details/72468875