MySQL处理插入过程中主键或唯一键重复值的解决办法

向MySQL插入数据有时会遇到主键重复的场景，原来的做法是先在程序代码中SELECT一下，判断是否存在指定主键或唯一键的数据，如果没有则插入，有的话则执行UPDATE操作，或另外一套逻辑，这种方法是不方便的。下面就介绍三种如何避免插入重复值的处理方法。

IGNORE

IGNORE的作用是插入的值遇到主键（PRIMERY KEY）或者唯一键（UNIQUE KEY）重复时自动忽略重复的数据，不影响后面数据的插入，即有则忽略，无则插入。，语法为：

INSERT IGNORE INTO ignore_tbl VALUES(1078,'MIKE');

简要介绍一下主键和唯一键的区别：

1、当一个属性声明为主键时，它将不接受NULL值。另一方面，当声明为Unique的属性时，它可以接受一个NULL值。

2、表中只能有一个主键，但可以有多个唯一键。

3、定义主键时自动创建聚簇索引。相反，Unique键生成非聚集索引。

--创建表
create table ignore_tbl 
(ID INT NOT NULL PRIMARY KEY,
NAME VARCHAR(10)
)DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.02 sec)

--插入一条数据
INSERT INTO ignore_tbl (ID,NAME) VALUES(1078,'BOB');
Query OK, 1 row affected (0.01 sec)

正常情况下，插入主键（ID）相同的数据，会报错：

--正常插入重复主键的数据，会报错
INSERT INTO ignore_tbl (ID,NAME) VALUES(1078,'MIKE');
1062 - Duplicate entry '1078' for key 'PRIMARY'

--数据也无法插入
SELECT * FROM ignore_tbl;
+------+------+
| ID   | NAME |
+------+------+
| 1078 | BOB  |
+------+------+
1 row in set (0.00 sec)

加入IGNORE后：

INSERT IGNORE INTO ignore_tbl VALUES(1078,'MIKE');
Query OK, 0 rows affected (0.00 sec)

--没有插入数据，忽略了主键重复值的插入
SELECT * FROM ignore_tbl;
+------+------+
| ID   | NAME |
+------+------+
| 1078 | BOB  |
+------+------+
1 row in set (0.00 sec)

插入数据不重复时，则可以正常插入：

INSERT IGNORE INTO ignore_tbl VALUES(1079,'MIKE');
Query OK, 1 row affected (0.00 sec)

SELECT * FROM ignore_tbl;
+------+------+
| ID   | NAME |
+------+------+
| 1078 | BOB  |
| 1079 | MIKE |
+------+------+
2 rows in set (0.00 sec)

REPLACE

该关键字的作用是当插入的记录遇到主键或者唯一键重复时先删除表中重复的记录行再插入，即有则删除+插入，无则插入，语法为：

REPLACE INTO replace_tbl() VALUES(); 

--建表+插入数据
CREATE TABLE replace_tbl(
ID INT NOT NULL PRIMARY KEY,
NAME VARCHAR(10))
DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.01 sec)
--插入
INSERT INTO replace_tbl VALUES(1079,'mike');
Query OK, 1 row affected (0.00 sec)
--目前的数据
SELECT * FROM replace_tbl;
+------+------+
| ID   | NAME |
+------+------+
| 1079 | mike |
+------+------+
1 row in set (0.00 sec)

现在使用REPLACE操作：

--相当于先删除，又插入
REPLACE INTO replace_tbl VALUES(1079,'BOB');
Query OK, 2 rows affected (0.00 sec)

--这时表中的数据
SELECT * FROM replace_tbl;
+------+------+
| ID   | NAME |
+------+------+
| 1079 | BOB  |
+------+------+
1 row in set (0.00 sec)

ON DUPLICATE KEY UPDATE

ON DUPLICATE KEY UPDATE 作用是当插入的记录遇到主键或者唯一键重复时，会执行后面定义的UPDATE操作。
相当于先执行Insert 操作，再根据主键或者唯一键执行update操作，即**有则更新，无则插入。**语法为：

INSERT INTO Tupdate() VALUES(1,1),(1,2) ON DUPLICATE KEY UPDATE NAME1=NAME1+1;

--建表
CREATE TABLE update_tbl(ID INT NOT NULL PRIMARY KEY,NAME VARCHAR(10))DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.01 sec)

--插入数据
INSERT INTO update_tbl VALUES(1079,'MIKE');
Query OK, 1 row affected (0.00 sec)

使用ON DUPLICATE KEY UPDATE操作：

INSERT INTO update_tbl VALUES(1079,"MIKE") ON DUPLICATE KEY UPDATE NAME='BOB';
Query OK, 2 rows affected (0.00 sec)

--表中的数据：
select * from update_tbl;
+------+------+
| ID   | NAME |
+------+------+
| 1079 | BOB  |
+------+------+
1 row in set (0.00 sec)

上面的操作相当于：

INSERT INTO update_tbl() VALUES(1079,"MIKE")
UPDATE update_tbl
SET NAME=’BOB‘
WHERE ID=1079; 

如果插入不重复的数据则不去做更新操作：

INSERT INTO update_tbl VALUES(1080,"MIKE") ON DUPLICATE KEY UPDATE NAME='BOB';
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from update_tbl;
+------+------+
| ID   | NAME |
+------+------+
| 1079 | BOB  |
| 1080 | MIKE |
+------+------+
2 rows in set (0.00 sec)

再举一个例子：

CREATE TABLE Tupdate
(ID INT NOT NULL PRIMARY KEY ,
NAME1 INT UNIQUE KEY
)default charset=utf8; 
INSERT INTO Tupdate() VALUES(1,1),(1,2) ON DUPLICATE KEY UPDATE NAME1=NAME1+1;
INSERT INTO Tupdate() VALUES(1,1),(1,2) ON DUPLICATE KEY UPDATE NAME1=VALUES(NAME1)+1; 

--第一条语句相当于：
INSERT INTO Tupdate() VALUES(1,1)
UPDATE Tupdate
SET NAME1=NAME1+1
WHERE ID=1; 

--第二条语句相当于：
INSERT INTO Tupdate() VALUES(1,1)
UPDATE Tupdate
SET NAME1=2+1
WHERE ID=1; 

在ON DUPLICATE KEY UPDATE后面使用VALUES指的就是已插入的记录的值，而不使用VALUES指的是表的自身值。
注意： ON DUPLICATE KEY UPDATE的后面执行的UPDATE更新的记录是WHERE重复的主键或者唯一键的ID，这点非常重要。
比如上张表而言：

INSERT INTO Tupdate() VALUES(1,1),(2,1) ON DUPLICATE KEY UPDATE NAME1=VALUES(ID)+1;
--相当于：
INSERT INTO Tupdate() VALUES(1,1)
UPDATE Tupdate
SET NAME1=2+1
WHERE ID=1;

也就是说，第二条记录(2,1)的1作为唯一键与第一条数据(1,1)的唯一键1重复了，因为(1,1)是先插入的，那么对它做更新操作（WHERE ID=1），取VALUES(ID)也就是2作+1操作NAME1=VALUES(ID)+1。总而言之，就是对已有数据进行更新操作，而不是新增数据。

参考文章：https://m.jb51.net/article/82441.htm