Mysql批量更新

Case1:更新同一字段为同一值

<update id="updateStatusByTableNameAndPartitions">
        update
            migrate_data
        set
            update_time = now(),
            migrate_status = #{migrateStatus,jdbcType=VARCHAR}
        where
            table_full_name = #{tableFullName,jdbcType=VARCHAR}
        and
            tablePartition in
        <foreach item="item" index="index" collection="partitions"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </update>

Case2:更新同一字段为不同值

UPDATE categories 
SET display_order = CASE id 
    WHEN 1 THEN 3 
    WHEN 2 THEN 4 
    WHEN 3 THEN 5 
END, 
title = CASE id 
    WHEN 1 THEN 'New Title 1'
    WHEN 2 THEN 'New Title 2'
    WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)

<update id="updateBatch" parameterType="java.util.List">
        update adhoc_timer_task
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="next_schedule_time =case" suffix="end,">
                <foreach collection="adhocTimerTasks" item="i" index="index">
                    <if test="i.nextScheduleTime!=null">
                        when id=#{i.id} then #{i.nextScheduleTime}
                    </if>
                </foreach>
            </trim>
        </trim>
        where id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.id,jdbcType=BIGINT}
        </foreach>
    </update>

参考：

1. https://www.jianshu.com/p/f71c88aca6fc