核心逻辑即为mysql批量插入数据
1 动态sql批量插入单表
mapper层 集合入参 入参字段符合动态sql字段
void updateBatchRefresh(List<WccsInfoPreviousDayResp> wccsInfoPreviousDayResps );
xml层
<update id="updateBatchRefresh" parameterType="java.util.List">
<!--@mbg.generated-->
update poc_extend_info
<trim prefix="set" suffixOverrides=",">
<trim prefix="wccs_supply_info_type = case" suffix="end,">
<foreach collection="list" index="index" item="item">
<if test="item.pocMiddleId != null">
when poc_middle_id = #{item.pocMiddleId,jdbcType=VARCHAR} then #{item.ghlx}
</if>
</foreach>
</trim>
<trim prefix="wccs_customer_group_code = case" suffix="end,">
<foreach collection="list" index="index" item="item">
<if test="item.pocMiddleId != null">
when poc_middle_id = #{item.pocMiddleId,jdbcType=VARCHAR} then #{item.chainStoresDm}
</if>
</foreach>
</trim>
</trim>
where poc_middle_id in
<foreach close=")" collection="list" item="item" open="(" separator=", ">
#{item.pocMiddleId,jdbcType=VARCHAR}
</foreach>
</update>
2 多表关联批量插入
两张表做关联,同时更新了 pei表的字段和 pbi表的两个字段。
日常开发中,一般都是用单表 UPDATE 语句,很少写多表关联的 UPDATE。
<update id="updateTable">
UPDATE poc_extend_info pei
INNER JOIN poc_base_info pbi ON pei.poc_middle_id = pbi.poc_middle_id
<trim prefix="set" suffixOverrides=",">
<trim prefix="pei.wccs_supply_info_type = case" suffix="end,">
<foreach collection="list" index="index" item="item">
<if test="item.pocMiddleId != null">
when pei.poc_middle_id = #{item.pocMiddleId,jdbcType=VARCHAR} then #{item.ghlx}
</if>
</foreach>
</trim>
<trim prefix="pei.wccs_customer_group_code = case" suffix="end,">
<foreach collection="list" index="index" item="item">
<if test="item.pocMiddleId != null">
when pei.poc_middle_id = #{item.pocMiddleId,jdbcType=VARCHAR} then #{item.chainStoresDm}
</if>
</foreach>
</trim>
<trim prefix="pbi.social_uniform_credit_code = case" suffix="end,">
<foreach collection="list" index="index" item="item">
<if test="item.pocMiddleId != null">
when pbi.poc_middle_id = #{item.pocMiddleId,jdbcType=VARCHAR} then #{item.socialCreditCode}
</if>
</foreach>
</trim>
</trim>
where pei.poc_middle_id in
<foreach close=")" collection="list" item="item" open="(" separator=", ">
#{item.pocMiddleId,jdbcType=VARCHAR}
</foreach>
</update>
1 在 MySQL 中,可以使用“UPDATE table1 t1,table2,...,table n”的方式来多表更新
mysql> UPDATE pbi p, pei pp SET pp.price = p.price * 0.8 WHERE p.productid= pp.productId;
Query OK, 5 rows affected (0.02 sec)
2 inner join
mysql> UPDATE pbi p, pei pp SET pp.price = p.price * 0.8 WHERE p.productid= pp.productId;
Query OK, 5 rows affected (0.02 sec)
3 left join
mysql> UPDATE product p, product_price pp SET pp.price = p.price * 0.8 WHERE p.productid= pp.productId;
Query OK, 5 rows affected (0.02 sec)
4 子查询
mysql> UPDATE pei pp SET price=(SELECT price*0.8 FROM pbi WHERE productid = pp.productid);
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0