MySQL 处理重复数据时 insert replace ON DUPLICATE KEY UPDATE

我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引，当我们再向这个表中使用已经存在的键值插入一条记录，那将会抛出一个主键冲突的错误，这个时候我们该怎么处理？

INSERT

INSERT IGNORE INTO books (name) VALUES ('MySQL Manual')

如果碰到主键或者唯一索引冲突的情况，则忽略执行，影响的行数为 0

INSERT INTO clients
(client_id,client_name,client_type)
SELECT 10345,'IBM','advertising'
FROM dual
WHERE not exists (select * from clients where clients.client_id=10345);

ON DUPLICATE KEY UPDATE

如果您指定了 ON DUPLICATE KEY UPDATE ，并且插入行后会导致在一个 UNIQUE 索引或 PRIMARY KEY 中出现重复值，则执行旧行 UPDATE。

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

这种情况不会生成新的索引

如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。当使用 ON DUPLICATE KEY UPDATE 时，DELAYED 选项被忽略。

REPLACE 语句

使用 REPLACE 插入一条记录时，如果不重 复，REPLACE 就和 INSERT 的功能一样，如果有重复记录，REPLACE 就使用新记录的值来替换原来的记录值。

使用 REPLACE 的最大好处就是可以将 DELETE 和 INSERT 合二为一，形成一个原子操作。这样就可以不必考虑在同时使用 DELETE 和 INSERT 时添加事务等复杂操作了。

在使用 REPLACE 时，表中必须有唯一索引，而且这个索引所在的字段不能允许空值，否则REPLACE就和 INSERT 完全一样的。

在执行 REPLACE 后，系统返回了所影响的行数，如果返回1，说明在表中并没有重复的记录，如果返回2，说明有一条重复记录，系统自动先调用了 DELETE 删除这条记录，然后再记录用INSERT来插入这条记录。如果返回的值大于2，那说明有多个唯一索引，有多条记录被删除和插入。这时候需要生成新的索引，REPLACE 的语法和INSERT非常的相似，如下面的REPLACE语句是插入或更新一条记录。

REPLACE INTO users (id,name,age) VALUES(123, ‘赵本山’, 50);

插入多条记录：

REPLACE INTO users(id, name, age) VALUES(123, ‘赵本山’, 50), (134,’Mary’,15);

上面曾提到 REPLACE 可能影响3条以上的记录，这是因为在表中有超过一个的唯一索引。在这种情况下，REPLACE 将考虑每一个唯一索引，并对每一 个索引对应的重复记录都删除，然后插入这条新记录。假设有一个 table1 表，有3个字段 a, b, c。它们都有一个唯一索引。

CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);

假设 table1 中已经有了3条记录

a b c
1 1 1
2 2 2
3 3 3

下面我们使用 REPLACE 语句向 table1 中插入一条记录。

REPLACE INTO table1(a, b, c) VALUES(1,2,3);

返回的结果如下

Query OK, 4 rows affected (0.00 sec)

在 table1 中的记录如下

a b c
1 2 3

则可以看到，REPLACE 将原先的3条记录都删除了，然后将（1, 2, 3）插入。