Three ways to avoid duplicate insertion of data in MySql

Preface

In the case of primary key conflict or unique key conflict in MySql, there are generally three insertion methods to avoid errors depending on the insertion method.

insert ignore.
replace into
insert on duplicate key update

insert ignore

insert ignore will ignore the data that already exists in the database. It will insert new data if there is no data in the database based on the primary key or unique index. If there is data, it will skip this data.

Small case

Table Structure

root:test> show create table t3G
*************************** 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)

Insert conflicting data

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

View Results

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)

replace into

replace into will try to insert the data first, and delete it if a conflict is found. Otherwise, do nothing.

Small case

root:test> show create table t3G
*************************** 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 conflicting data

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 is gone and a new record has appeared.

insert on duplicate key update

If insert on duplicate key update is specified at the end of the insert into statement, if duplicate values appear, update will be performed after the duplicate values appear.

case

root:test> show create table t3G
*************************** 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 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)

It can be seen that the record with id=3 has changed, c1=original c1+3, and other columns have not changed.

The above are the details of four MySql methods to avoid duplicate data insertion. For more information about MySQL to avoid inserting duplicate data, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in: