mysql dual 使用虚拟表实现不重复数据插入

最新推荐文章于 2023-07-28 14:50:38 发布

晴空恬静

最新推荐文章于 2023-07-28 14:50:38 发布

阅读量531

点赞数 1

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/u011036433/article/details/83899922

版权

mysql 专栏收录该内容

4 篇文章

订阅专栏

这篇博客探讨了在MySQL中如何在不考虑主键的情况下避免插入重复数据的方法，包括INSERT IGNORE、REPLACE INTO、ON DUPLICATE KEY UPDATE以及利用NOT EXISTS子查询的技巧。这些方法各有特点，例如INSERT IGNORE会忽略重复数据，而ON DUPLICATE KEY UPDATE则允许在冲突时更新已有记录。最后，推荐使用NOT EXISTS结合DUAL表的策略，因为它提供了更大的灵活性来定义唯一性条件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景：当插入数据时，需要判断某些字段在表中不存在相同的数据，但是id(主键)不做为判断条件

问题：1.insert into

语句会判断主键、unique，当出现重复值时，报错

2.INSERT IGNORE INTO

相比insert into 的区别是：INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

3.REPLACE INTO

如果存在primary or unique相同的记录，则先删除掉。再插入新记录。
REPLACE INTO books SELECT 1, 'MySQL Manual' FROM books
这种方法就是不管原来有没有相同的记录，都会先删除掉然后再插入。

4.on duplicate key update
当primary或者unique重复时，则执行update语句，如update后为无用语句，如id=id，则同1功能相同，但错误不会被忽略掉。例如，为了实现name重复的数据插入不报错，可使用一下语句：
INSERT INTO books (name) VALUES ('MySQL Manual') ON duplicate KEY UPDATE id = id
这种方法有个前提条件，就是，需要插入的约束，需要是主键或者唯一约束（在你的业务中那个要作为唯一的判断就将那个字段设置为唯一约束也就是unique key）。

5.insert … select … where not exist
根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件。例如：
insert into test(id,name,password) select 9,"zhangsan1","1234563" from DUAL where not exists(select name from test where name = "zhangsan1" and password="1234563");
这种方法其实就是使用了mysql的一个临时表的方式，但是里面使用到了子查询，效率也会有一点点影响，如果能使用上面的就不使用这个。

语法：

INSERT INTO table(column1,column2,column3 ...columnN)  
SELECT value1,value2,value3 ...valueN  
FROM dual  
WHERE NOT EXISTS(  
      SELECT *  
      FROM table  
      WHERE value = ?  
);

结论：使用最后一种方式