mysql - MYSQL：如何避免插入重复记录？

Question

我正在做如下插入语句：

Insert into table2 (host, ip, domain, statusnew) 
select hostname, ip, domain, "True" from table1 t1, table2 t2 
where t1.status = "Done"
and t2.statusnew not regexp "True"
limit 10
;

我添加了该语句 t2.statusnew not regexp "True"只是为了确保没有重复插入。但是，它正在添加重复的行。

如何确保没有重复条目？

标签： mysqljoinleft-joininner-joincrud

Answer 1

INSERT INTO TABLE_2
  (id, name)
SELECT t1.id,
       t1.name
  FROM TABLE_1 t1
 WHERE NOT EXISTS(SELECT id FROM TABLE_2 t2 WHERE t2.id = t1.id)