mysql批量插入不重复数据

1.设置唯一建
前提是：有唯一建可用，but基本很难有这样的场景。
datum有唯一建。

CREATE TABLE `datum` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mid` int(11) NOT NULL DEFAULT '0',
  `rong_liang` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `yong_liang` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `level` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `adapt_niandu` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `perrfect_niandu` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `datum_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `oil_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `buchong2` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `buchong3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `mid` (`mid`)
) ENGINE=InnoDB AUTO_INCREMENT=1048582 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

//插入语句
insert into datum(mid) VALUES(100071111) ON DUPLICATE KEY UPDATE rong_liang = "2"

2.使用临时表dual

前提：组装好数据resultList，每行数据是一个DUAL，使用
UNION ALL成为一张新表a，插入的时候排除掉tables里面已经有的数据。

  insert into tables(audit_id,acc_id,mid,model_memo_id,operate_type,common_group_id,position_id,creator,modifier)
     select #{auditId},#{accId},mid,memoId,#{operateType},#{groupId},positionId,#{creator},#{creator} from(
     select * from (
    <foreach collection="resultList" item="res" separator="UNION ALL">
      SELECT #{res.mid} mid, #{res.memoId} memoId, #{res.positionId} positionId FROM DUAL
    </foreach>) a
    where not exists(select 1 from tables w
    where w.acc_id=#{accId} and w.audit_id =#{auditId} and w.mid=a.mid and w.model_memo_id=a.memoId and w.position_id = a.positionId and w.is_deleted = 0)) b

3. 从另一张表插入不重复的数据插入到新表

insert into table2(audit_id, acc_id, mid, position_id, model_memo_id, operate_type)
    select * from
    (select audit_id, acc_id, mid, position_id, model_memo_id, operate_type
    from table1 a
		where a.audit_id =26 and a.is_deleted = 0 and not exists (SELECT 1 from table2 b
    where b.is_deleted = 0 and b.audit_id = a.audit_id and a.acc_id = b.acc_id and a.mid = b.mid 
		and a.position_id = b.position_id and a.model_memo_id = b.model_memo_id and a.operate_type = b.operate_type)
		) c

eg：
insert into tb(newsid,a,b) select newsid,a,b from ta a where not exists(select 1 from tb b where a.newsid=b.newsid and a.a=b.a)