防重复插入大致有以下几种:
insert ignore into: 结合主键或者唯一索引
on duplicate key update
insert into … select … where not exists
replace into
注意:这里主要对<foreach> 中 separator用union all
<insert id="insertBatch">
insert into t_activity_obj (activity_id, target_id, target_type, sort)
<foreach collection="targets" item="item" separator="union all">
( select #{activityId}, #{item.targetId}, #{item.targetType}, ifnull(#{item.sort}, 999)
from dual
where not exists (
select activity_obj_id from t_activity_obj
where activity_id=#{activityId} and target_id=#{item.targetId}
and target_type=#{item.targetType})
)
</foreach>
</insert>