请问mysql用mybatis循环执行3万条数据有什么办法快一点

Question

<foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update total_score set score=#{item.score}
        where user_id =(select id from sys_admin where uk_account=#{item.username})
        and time=#{item.time}
        </foreach>

像这样执行要40多秒，怎么能快一些呢？

Answer 1

你参照这个写法试试

复制代码
<update id="batchUpdate" parameterType="list">
         update songs
            <trim prefix="set" suffixOverrides=",">
                 <trim prefix="path =case" suffix="end,">
                     <foreach collection="list" item="i" index="index">
                             <if test="i.songid!=null">
                                  when songid=#{i.songid} then #{i.path}
                             </if>
                     </foreach>
                  </trim>
             </trim>
            where songid in 
            <foreach collection="list"  item="i" index="index" open="(" separator="," close=")">
              #{i.songid}
          </foreach>
    </update>