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

HTML table markup tutorial (37): background image attribute BACKGROUND

Set the background image for the table header. Yo...

Detailed explanation of JavaScript prototype chain

Table of contents 1. Constructors and instances 2...

Analysis and summary of the impact of MySQL transactions on efficiency

1. Database transactions will reduce database per...

Detailed explanation of how to restore database data through MySQL binary log

Website administrators often accidentally delete ...

Solve the conflict between docker and vmware

1. Docker startup problem: Problem Solved: You ne...

Mysql date formatting and complex date range query

Table of contents Preface Query usage scenario ca...

Detailed explanation of the wonderful uses of SUID, SGID and SBIT in Linux

Preface Linux's file permission management is...

Implementation example of JS native double-column shuttle selection box

Table of contents When to use Structural branches...

36 principles of MySQL database development (summary)

Preface These principles are summarized from actu...

A brief discussion on VUE uni-app's commonly used APIs

Table of contents 1. Routing and page jump 2. Int...

Implementing a simple carousel based on JavaScript

This article shares the specific code of JavaScri...

CSS implementation code for drawing triangles (border method)

1. Implement a simple triangle Using the border i...

Example code for setting hot links and coordinate values ​​for web images

Sometimes you need to set several areas on a pict...

Summary of common functions and usage methods of WeChat applet development

Here, I have mainly sorted out some commonly used...