In case of primary key conflict or unique key conflict in mysql, there are generally three avoidance methods according to different insertion strategies. 1,insert ignore 2,replace into 3,insert on duplicate key update
Note that unless the table has a PRIMARY KEY or UNIQUE index, using the above three statements is meaningless, which is the same as using simple INSERT INTO.
1, insert ignore
insert ignore will ignore the existing data in the database (according to the primary key or unique index). If there is no data in the database, insert new data. If there is data, skip this data
Case:
The structure of the table is as follows:
root:test> show create table t3\G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) DEFAULT NULL,
`c2` varchar(20) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uidx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root:test> select * from t3;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | a | 1 |
| 2 | 2 | a | 1 |
| 8 | NULL | NULL | 1 |
| 14 | 4 | bb | NULL |
| 17 | 5 | cc | 4 |
+----+------+------+------+
5 rows in set (0.00 sec)Test inserting data with unique key conflicts
root:test> insert ignore into t3 (c1,c2,c3) values(5,'cc',4),(6,'dd',5); Query OK, 1 row affected, 1 warning (0.01 sec) Records: 2 Duplicates: 1 Warnings: 1
As shown below, you can see that only (6,'dd',5) is inserted, and there is a warning prompt with duplicate values.
root:test> show warnings; +---------+------+---------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------+ | Warning | 1062 | Duplicate entry '5' for key 'uidx_c1' | +---------+------+---------------------------------------+ 1 row in set (0.00 sec) root:test> select * from t3; +----+------+------+------+ | id | c1 | c2 | c3 | +----+------+------+------+ | 1 | 1 | a | 1 | | 2 | 2 | a | 1 | | 8 | NULL | NULL | 1 | | 14 | 4 | bb | NULL | | 17 | 5 | cc | 4 | | 18 | 6 | dd | 5 | +----+------+------+------+ 6 rows in set (0.00 sec)
Re query the table structure and find that although only one record is added, AUTO_INCREMENT has increased by 2 (18 to 20)
root:test> show create table t3\G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) DEFAULT NULL,
`c2` varchar(20) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uidx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)2, replace into
- replace into first attempts to insert data into the table. If you find that this row of data already exists in the table (judged according to the primary key or unique index), delete this row of data first, and then insert new data. Otherwise, insert new data directly.
- To use replace into, you must have delete and insert permissions
Case:
root:test> show create table t3\G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) DEFAULT NULL,
`c2` varchar(20) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uidx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root:test> select * from t3;
+----+------+--------+------+
| id | c1 | c2 | c3 |
+----+------+--------+------+
| 1 | 1 | cc | 4 |
| 2 | 2 | dd | 5 |
| 3 | 3 | qwewqe | 3 |
+----+------+--------+------+
3 rows in set (0.00 sec)Insert a piece of data that conflicts with the unique key (column c1) of record id=3
root:test> replace into t3 (c1,c2,c3) values(3,'new',8); Query OK, 2 rows affected (0.02 sec) root:test> select * from t3; +----+------+------+------+ | id | c1 | c2 | c3 | +----+------+------+------+ | 1 | 1 | cc | 4 | | 2 | 2 | dd | 5 | | 4 | 3 | new | 8 | +----+------+------+------+ 3 rows in set (0.00 sec)
You can see that the original record with id=3 and c1=3 is missing, and a new record with id=4 and c1=3 is added After the replace into statement is executed, a number will be returned to indicate the number of rows affected. This number is the sum of the number of deleted and inserted rows. In the above example, 2 rows affected
3, insert on duplicate key update
- If on duplicate key update is specified at the end of the insert into statement, and the insertion of a row will result in duplicate values in a UNIQUE index or PRIMARY KEY, UPDATE will be executed on the row with duplicate values; If there is no duplication problem, insert a new line, just like the ordinary insert into.
- To use insert into, you must have insert and update permissions
- If a new record is inserted, the value of the affected row displays 1; If the original record is updated, the value of the affected line displays 2; If the value is the same before and after the record is updated, the value of the number of affected rows displays 0
Case:
root:test> show create table t3\G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) DEFAULT NULL,
`c2` varchar(20) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uidx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root:test> select * from t3;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | fds | 4 |
| 2 | 2 | ytu | 3 |
| 3 | 3 | czx | 5 |
+----+------+------+------+
3 rows in set (0.00 sec)Insert a piece of data that conflicts with the unique key (column c1) of record id=3
root:test> insert into t3(c1,c2,c3) values (3,'new',5) on duplicate key update c1=c1+3; Query OK, 2 rows affected (0.01 sec) root:test> select * from t3; +----+------+------+------+ | id | c1 | c2 | c3 | +----+------+------+------+ | 1 | 1 | fds | 4 | | 2 | 2 | ytu | 3 | | 3 | 6 | czx | 5 | +----+------+------+------+ 3 rows in set (0.00 sec)
You can see that the record with id=3 has changed, c1 = the original c1+3, and other columns have not changed.
Conclusion:
- All three methods can avoid the problem of insertion failure caused by duplicate primary key or unique index.
- insert ignore can ignore duplicate data and insert only non duplicate data.
- Replace into and insert On duplicate key update is to replace the original duplicate data. The difference is that replace into is to insert a new line after deleting the original line. If there is a self incremented id, it will change the self incremented id; insert ... on duplicate key update when a duplicate row is encountered, the original row will be directly updated. The specific fields to be updated and how to update depend on the statement after update.