mysql批量更新

例如我们平时使用mysql的批量更新都是这样的

    <foreach collection="list" item="p" index="index" separator=";">
        UPDATE product_third_code_mapping
        SET stock_num = #{p.stockNum},
        update_time = NOW()
        WHERE source_channel = #{p.channelCode}
        AND third_product_code = #{p.skuId}
    </foreach>

但是这样的sql会影响效率
所以我们需要结合case when then else end来进行优化

 UPDATE product
        SET third_merchant_product_code = ( CASE
        <foreach collection="poList" item="p" separator=" ">
            WHEN id = #{p.storeProductId} then #{p.thirdProductCode}
        </foreach>
        ELSE 0 END )
        WHERE
        is_deleted = 0
        AND id IN (
        <foreach collection="idsList" item="p" separator=",">
            #{p}
        </foreach>
        )

如果需要更新多个字段

   update im_virtual_channel_stock iv
        set iv.virtual_stock_num = (
        case
        <foreach collection="poList" item="p" separator="   ">
            when iv.item_id = #{p.storeProductId} then #{p.stockNum}
        </foreach>
        else 0 end
        ),
        iv.virtual_available_stock_num = (
        case
        <foreach collection="poList" item="p" separator="   ">
            when iv.item_id = #{p.storeProductId} then #{p.stockNum} - iv.freeze_stock_num
        </foreach>
        else 0 end
        )
        where iv.is_available=1
        and iv.is_deleted=0
        and iv.item_id in (
        <foreach collection="poList" item="p" separator=",">
            #{p.storeProductId}
        </foreach>
        )