Three ways to avoid duplicate insertion of data in MySql

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.

  1. insert ignore.
  2. replace into
  3. 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:
  • Three ways to prevent MySQL from inserting duplicate data
  • MySQL uses UNIQUE to implement non-duplicate data insertion
  • How to prevent MySQL from inserting duplicate records
  • Several ways to avoid duplicate insertion of records in MySql
  • 4 ways to avoid duplicate insertion of data in Mysql

<<:  Distinguishing between Linux hard links and soft links

>>:  A brief discussion on the perfect adaptation solution for Vue mobile terminal

Recommend

How to install MySQL Community Server 5.6.39

This article records the detailed tutorial of MyS...

MySQL-group-replication configuration steps (recommended)

MySQL-Group-Replication is a new feature develope...

MySQL uses the truncate command to quickly clear all tables in a database

1. Execute the select statement first to generate...

Detailed Example of MySQL InnoDB Locking Mechanism

1. InnoDB locking mechanism The InnoDB storage en...

How to use JavaScript strategy pattern to validate forms

Table of contents Overview Form validation withou...

Linux platform mysql enable remote login

During the development process, I often encounter...

HTML is actually the application of learning several important tags

After the article "This Will Be a Revolution&...

Vue3 Documentation Quick Start

Table of contents 1. Setup 1. The first parameter...

jQuery implements percentage scoring progress bar

This article shares the specific code of jquery t...

Summary and analysis of commonly used Docker commands and examples

Table of contents 1. Container lifecycle manageme...

Solution to the ineffectiveness of flex layout width in css3

Two-column layout is often used in projects. Ther...

Detailed explanation of three ways to set borders in HTML

Three ways to set borders in HTML border-width: 1...

Vue globally introduces scss (mixin)

Table of contents 1. mixin.scss 2. Single file us...

How to create https using nginx and Tencent Cloud free certificate

I have been studying how to get https. Recently I...