DUPLICATE KEY UPDATE 循环使用(批量新增或更新时)
当批量新增或更新数据时 DUPLICATE KET UPDATE 语法 这个语法首先要在数据库表里设定除了主键以外 一个字段的 UNIQUE索引。
批量操作 实现代码
<insert id="batchSaveCommissionSummaryList" parameterType="list" > insert into commission_summary (enterprise_id,enterprise_name,in_province_amount,in_province_rate,in_province_commission,out_province_amount,out_province_rate, out_province_commission,total_amount,total_commission,month,create_time) values <foreach collection="list" item="item" index="index" separator=","> (#{item.enterpriseId},#{item.enterpriseName},#{item.inProvinceAmount},#{item.inProvinceRate},#{item.inProvinceCommission},#{item.outProvinceAmount}, #{item.outProvinceRate},#{item.outProvinceCommission},#{item.totalAmount},#{item.totalCommission},#{item.month},#{item.createTime}) </foreach> ON DUPLICATE KEY UPDATE enterprise_id = values(enterprise_id), enterprise_name = values(enterprise_name), in_province_amount = values(in_province_amount), in_province_rate = values(in_province_rate), in_province_commission = values(in_province_commission), out_province_amount = values(out_province_amount), out_province_rate =values(out_province_rate), out_province_commission = values(out_province_commission), total_amount = values(total_amount), total_commission = values(total_commission), month = values(month), create_time = values(create_time) </insert>
下班记得打卡