【Mybatis】【Oracle】批量更新探讨批量更新数据三种写法的效率问题。
实现⽅式有三种
⼀、⽤for循环，通过循环传过来的参数集合，循环出N条sql
注意：该法要想成功，需要（针对mysql）在db链接url后⾯带⼀个参数  &allowMultiQueries=true，即：  jdbc:mysql://localhost:3306/mysqlTest?characterEncoding=utf-8&allowMultiQueries=true
<!-- 批量更新第⼀种⽅法，通过接收传进来的参数list进⾏循环着组装sql -->
<update id="updateBatch" parameterType="java.util.List" >
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update standard_relation
<set>
<if test="item.standardFromUuid != null" >
standard_from_uuid = #{item.standardFromUuid,jdbcType=VARCHAR},
</if>
<if test="item.standardToUuid != null" >
standard_to_uuid = #{item.standardToUuid,jdbcType=VARCHAR},
</if>
<if test="Modified != null" >
gmt_modified = #{Modified,jdbcType=TIMESTAMP},
</if>
</set>
where id = #{item.id,jdbcType=BIGINT}
</foreach>
</update>
⼆、⽤ 条件判断变相的进⾏批量更新
<!-- 批量更新第⼆种⽅法，通过 case when语句变相的进⾏批量更新 -->
<update id="updateBatch" parameterType="java.util.List" >
update standard_relation
<trim prefix="set" suffixOverrides=",">
<trim prefix="standard_from_uuid =case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.standardFromUuid!=null">
when id=#{i.id} then #{i.standardFromUuid}
</if>
</foreach>
</trim>
<trim prefix="standard_to_uuid =case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.standardToUuid!=null">
when id=#{i.id} then #{i.standardToUuid}
</if>
</foreach>
</trim>
<trim prefix="gmt_modified =case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.gmtModified!=null">
when id=#{i.id} then #{i.gmtModified}
</if>
</foreach>
</trim>
</trim>
where
<foreach collection="list" separator="or" item="i" index="index" >
id=#{i.id}
</foreach>
</update>
注意：
如果list为空，但是也需要更新，那么就会报错；因为拼装后的sql语句是update standardRelation where id = ，没有set关键字；此时解决⽅法就是把最内层的<if>判断去掉，⽽此时，该字段可以被更新为null。
三、⽤ON DUPLICATE KEY UPDATE进⾏批量更新
批量更新第三种⽅法，⽤ON DUPLICATE KEY UPDATE
<insert id="updateBatch" parameterType="java.util.List">
insert into standard_relation(id,relation_type, standard_from_uuid,standard_to_uuid, relation_score, stat,last_process_id, is_deleted, gmt_created, gmt_modified,relation_desc)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(#{item.id,jdbcType=BIGINT},
#{lationType,jdbcType=VARCHAR},
#{item.standardFromUuid,jdbcType=VARCHAR},
#{item.standardToUuid,jdbcType=VARCHAR},
批量更新sql语句#{lationScore,jdbcType=DECIMAL},
#{item.stat,jdbcType=TINYINT},
#{item.lastProcessId,jdbcType=BIGINT},
#{item.isDeleted,jdbcType=TINYINT},
#{Created,jdbcType=TIMESTAMP},
#{Modified,jdbcType=TIMESTAMP},
#{lationDesc,jdbcType=VARCHAR})
</foreach>
ON DUPLICATE KEY UPDATE
id = VALUES(id),
relation_type = VALUES(relation_type),
standard_from_uuid = VALUES(standard_from_uuid),
standard_to_uuid = VALUES(standard_to_uuid),
relation_score = VALUES(relation_score),
stat = VALUES(stat),
last_process_id = VALUES(last_process_id),
is_deleted = VALUES(is_deleted),
gmt_created = VALUES(gmt_created),
gmt_modified = VALUES(gmt_modified),
relation_desc = VALUES(relation_desc)
</insert>
效率测试
@Override
public void updateStandardRelations() {
List<StandardRelation> list = standardRelationMapper.selectByStandardUuid("xiemingjieupdate");
for(StandardRelation tmp : list){
tmp.StandardFromUuid()+"update");
tmp.StandardToUuid()+"update");
}
long begin=System.currentTimeMillis();
standardRelationManager.updateBatch(list);
long end=System.currentTimeMillis();
System.out.print("当前的批量更新的⽅法⽤时"+(end-begin)+"ms");
}
结果⽐较
结果说明
sql语句for循环效率其实相当⾼的，因为它仅仅有⼀个循环体，只不过最后update语句⽐较多，量⼤了就有可能造成sql阻塞。
case when虽然最后只会有⼀条更新语句，但是xml中的循环体有点多，每⼀个case when 都要循环⼀遍list集合，所以⼤批量拼sql的时候会⽐较慢，所以效率问题严重。使⽤的时候建议分批插⼊。
duplicate key update可以看出来是最快的，但是⼀般⼤公司都禁⽤，公司⼀般都禁⽌使⽤replace into
和INSERT INTO … ON DUPLICATE KEY UPDATE，这种sql有可能会造成数据丢失和主从上表的⾃增id值不⼀致。⽽且⽤这个更新时，记得⼀定要加上id，⽽且values（）括号⾥⾯放的是数据库字段，不是java对象的属性字段。
根据效率，安全⽅⾯综合考虑，选择适合的很重要。
参考⽂章

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。