mysql from dual插入实现不插入重复记录

INSERT INTO user  (id, no,add_time,remark)
select * from (
SELECT 1 id, 1 no, NOW() add_time,‘1,2,3,1,2‘ remark FROM DUAL 
UNION ALL 
SELECT 1 no, 2 no, NOW() add_time,‘1,2,3,1,2‘ remark FROM DUAL
UNION ALL
SELECT 1 no, 3 no, NOW() add_time,‘1,2,3,1,2‘ remark FROM DUAL
) a where not exists (select no from user b where a.no = b.no)

上述是实现user表的no字段不重复，插入三条记录。

下面是mybatis批量写入no字段不重复的实现语句。

INSERT INTO user (id, no,add_time,result)
select * from (
<foreach collection="list" item="obj" separator=" UNION ALL " >
SELECT #{obj.id} id, #{obj.no} no, #{obj.addTime} add_time,#{obj.remark} remark FROM DUAL
</foreach>
) a where not exists (select no from user b where a.no = b.no)

只是完成了实现，还不了解其中原理。

mysql from dual插入实现不插入重复记录

标签：唯一性   mysq   字段   索引   sep   round   union all   添加   重复