三种MySql避免重复插入记录方法

发表于   |   分类于

mysql在存在主键冲突或者唯一键冲突的情况下，根据插入策略不同，一般有以下三种避免方法:
1、insert ignore
2、replace into
3、insert on duplicate key update

注意，除非表有一个PRIMARY KEY或UNIQUE索引，否则，使用以上三个语句没有意义，与使用单纯的INSERT INTO相同。

insert ignore

  • insert ignore会忽略数据库中已经存在的数据(根据主键或者唯一索引判断)，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。
1
2
INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) 
VALUES ('[email protected]', '99999', '9999');

这样当有重复记录就会忽略,执行后返回数字0

还有个应用就是复制表,避免重复记录：

1
INSERT IGNORE INTO `table_1` (`name`) SELECT `name` FROM `table_2`;

replace into

  • replace into 首先尝试插入数据到表中。 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据，然后插入新的数据，否则，直接插入新数据。
  • 使用replace into，你必须具有delete和insert权限。
1
2
3
REPLACE INTO `table_name`(`col_name`, ...) VALUES (...);
REPLACE INTO `table_name` (`col_name`, ...) SELECT ...;
REPLACE INTO `table_name` SET `col_name`='value',...

insert on duplicate key update

  • 如果在insert into 语句末尾指定了on duplicate key update，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致重复的问题，则插入新行，跟普通的insert into一样。

    1
    2
    3
    4
    5
    --如果列a被定义为UNIQUE，并且包含值1，则以下两个语句具有相同的效果
    INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3) 
    ON DUPLICATE KEY UPDATE `c`=`c`+1; 

    UPDATE `table` SET `c`=`c`+1 WHERE `a`=1;

  • 使用insert into，你必须具有insert和update权限。

  • 如果有新记录被插入，则受影响行的值显示1；如果原有的记录被更新，则受影响行的值显示2；如果记录被更新前后值是一样的，则受影响行数的值显示0。

  • 当您使用on duplicate key update时，insert delayed (延迟插入)选项被忽略。

结论

  1. 这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。

  2. insert ignore 能忽略重复数据，只插入不重复的数据。

  3. replace into 和 insert … on duplicate key update 都是替换原有的重复数据。

  4. replace into 是删除原有的行后，在插入新行，如有自增id，这个会造成自增id的改变；

  5. insert … on duplicate key update在遇到重复行时，会直接更新原有的行，具体更新哪些字段怎么更新，取决于update后的语句。

  6. 特别说明：UNIQUE索引将会对null字段失效，也就是说(a字段上建立唯一索引)：INSERT INTOtest(a) VALUES (NULL); 是可以重复插入的（联合唯一索引也一样）。

本文部分出自 - MySQL避免插入重复记录的方法