mysql 防止重复插入唯一限制的数据

一. insert ignore into

当插入数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。所以使用ignore请确保语句本身没有问题，否则也会被忽略掉。例如：

INSERT IGNORE INTO books (name) VALUES ('张三')

这种方法很简便，但是有一种可能，就是加入不是因为重复数据报错，而是因为其他原因报错的，也同样被忽略了～
比如：某个字段被设置了 not null 并没有设置默认值，是为了防止插入时漏掉这个字段，但是使用INSERT IGNORE后，如果没有设置这个字段默认值，将不会报错，会自动设置一个没有用的默认值，如int，则自动默认值变为0，varchar则自动变为''（空字符串）。

二. on duplicate key update

当primary或者unique重复时，则执行update语句，如update后为无用语句，如id=id，则同1功能相同，但错误不会被忽略掉。例如，为了实现name重复的数据插入不报错，可使用一下语句：

INSERT INTO books (name) VALUES ('张三') ON duplicate KEY UPDATE id = id

这种方法有个前提条件，就是，需要插入的约束，需要是主键或者唯一约束。

三. insert … select … where not exist

根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件。例如：

INSERT INTO content (  
    detail,  
    status,  
    beginTime,  
    endTime)   
SELECT  
    @detail,  
    1,  
    NULL,  
    NULL  
FROM DUAL  
    WHERE NOT EXISTS(  
        SELECT contentId   
        FROM content   
        WHERE detail=@detail);

通用例子：

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

四. replace into

replace into 插入数据时，首先会根据主键(Primary Key )或者唯一索引(Unique)判断是否存在重复数据。

有重复数据，先删除此行数据，然后插入新的数据
无重复数据，直接插入新数据
REPLACE语句返回受影响的行数。这个行数是删除和插入的行的总和。如果计数为1，说明 REPLACE则插入一行并且不删除任何行。如果计数大于1，则在插入新行之前删除一个或多个旧行。

弊端：

把旧记录删除之后，插入的新记录只是插入了那些指定的字段，原本不想更新的字段，直接为默认值了，会导致数据丢失。
replace中执行delete时, 在有外键的情况下会很麻烦，更新后新记录会产生新的id，导致这种关联丢失。
replace每次要重新分配自增id，很容易导致id值范围不够用。
若是数据库存在主从关系，在主机器上进行了replace into操作之后，从机器上对应表的AUTO_INCREMENT是不会更新的，导致从机器转为主机器时，新插入数据会出现异常，直到AUTO_INCREMENT增加到原来主机器的值为止。
replace into 匹配到条件以后会先delete然后insert,而不是执行update,效率较update低很多。
如果delete时定义的有触发器, 则会被执行;
副作用也会被传播到replica slave.

对于某些场景，比如三个字段(A, B, C), A为唯一索引, 重复的情况下你不想改动B, 则:

insert into table1(A, B, C) values(1,2,3) on duplicate key update C=3;

效率上来说, INSERT ... ON DUPLICATE KEY UPDATE 比replace 要好, 毕竟replace如果重复则先删除再插入.

最后编辑于：2020.07.22 11:43:01