Detailed explanation of the solution to duplicate insertion of MySQL primary key and unique key

Detailed explanation of the solution to duplicate insertion of MySQL primary key and unique key

When we insert data, we may encounter the problem of inserting duplicate data, but these data are not allowed to have duplicate values:

CREATE TABLE stuInfo (
  id INT NOT NULL COMMENT 'Serial number',
  name VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'Name',
  age INT NOT NULL DEFAULT 0 COMMENT 'Age',
  PRIMARY KEY (id),
  UNIQUE KEY uniq_name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='student table';
mysql> INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25),(2,'aa',24);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Solution:

1. IGNORE

Use ignore to automatically ignore duplicate records when the inserted value encounters a duplicate primary key (PRIMARY KEY) or unique key (UNIQUE KEY), without affecting the insertion of subsequent records.

INSERT IGNORE INTO stuInfo (name,birthday,is_deleted) VALUES ('yoona','1990-01-05',0),('aa','1990-01-16',0),('bb','1990-01-17',0);

Running results:

mysql> INSERT IGNORE INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25),(2,'aa',24);
Query OK, 2 rows affected (0.02 sec)
Records: 3 Duplicates: 1 Warnings: 0
mysql> select * from stuInfo; 
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | yoona | 20 |
| 2 | aa | 24 |
+----+-------+-----+
2 rows in set (0.00 sec)

We can see from the running results that only two rows are affected, which means that (1,'yoona',20) data is inserted, (1,'xiaosi',25) duplicate data is automatically ignored, and (2,'aa',24) non-duplicate data continues to be inserted and will not be affected by the duplicate data;

2. REPLACE

When using replace to insert records with duplicate primary keys or unique keys, delete the duplicate records in the table before inserting them.

mysql> REPLACE INTO stuInfo (name,birthday,is_deleted) VALUES ('yoona','1990-01-15',0),('yoona','1990-02-16',0),('aa','1990-01-13',0);
Query OK, 4 rows affected (0.02 sec)
Records: 3 Duplicates: 1 Warnings: 0
Running results:
 
mysql> select * from stuInfo; +----+-------+------------+------------+
| id | name | birthday | is_deleted |
+----+-------+------------+------------+
| 21 | yoona | 1990-02-16 | 0 |
| 22 | aa | 1990-01-13 | 0 |
+----+-------+------------+------------+
2 rows in set (0.00 sec)

From the output information, we can see that 4 rows are affected, which means that ('yoona','1990-01-15',0) was inserted first and then ('yoona','1990-01-15',0) was deleted.

3. ON DUPLICATE KEY UPDATE

When the inserted record encounters a duplicate primary key or unique key, the UPDATE operation defined later will be executed. It is equivalent to performing an Insert operation first, and then performing an update operation based on the primary key or unique key.

DROP TABLE IF EXISTS stuInfo;
CREATE TABLE stuInfo (
  id INT NOT NULL COMMENT 'Serial number',
  name VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'Name',
  age INT NOT NULL DEFAULT 0 COMMENT 'Age',
  PRIMARY KEY (id),
  UNIQUE KEY uniq_name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='student table';

VALUES explanation after ON DUPLICATE KEY UPDATE:

VAULES(age) refers to the value of the record to be inserted

Age refers to the table's own value, the inserted value.

(1) First Scenario:

#VALUES(age) value to be inserted 25
INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25) ON DUPLICATE KEY UPDATE age = VALUES(age) + 1;

is equivalent to:

INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20);
UPDATE stuInfo 
SET age = VALUES(age) + 1
WHERE id = 1;

Running results:

mysql> INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25) ON DUPLICATE KEY UPDATE age = VALUES(age) + 1;
Query OK, 3 rows affected (0.01 sec)
Records: 2 Duplicates: 1 Warnings: 0
mysql> select * from stuInfo;   
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | yoona | 26 |
+----+-------+-----+
1 row in set (0.00 sec)

(2) The second situation:

#age value 20 has been inserted
INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25) ON DUPLICATE KEY UPDATE age = age + 1;

is equivalent to:

INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20);
UPDATE stuInfo 
SET age = age + 1
WHERE id = 1;

Running results:

mysql> INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25) ON DUPLICATE KEY UPDATE age = age + 1;
Query OK, 3 rows affected (0.02 sec)
Records: 2 Duplicates: 1 Warnings: 0
mysql> select * from stuInfo;
 +----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | yoona | 21 |
+----+-------+-----+
1 row in set (0.00 sec)

If you encounter duplicate inserted data, ON DUPLICATE KEY UPDATE is used to modify the inserted data. You can use it to get duplicate inserted data (directly use the field name) or get duplicate data to be inserted (values ​​(field name)). We will not insert duplicate data.

Repeat the inserted data: (1,'yoona',20) in the above example

Repeat the data to be inserted: (1,'yoona',25) in the above example

This is the end of this article on the detailed explanation of the solution to the duplicate insertion of MySQL primary key and unique key. For more relevant content on duplicate insertion of MySQL primary key and unique key, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • 4 ways to avoid duplicate insertion of data in Mysql
  • Three ways to prevent MySQL from inserting duplicate data
  • Three ways to avoid duplicate insertion of data in MySql
  • mysql stored procedure determines duplicate data and does not insert it
  • Share the method of ignoring duplicate data when inserting data in MYSQL
  • Several ways to avoid duplicate insertion of records in MySql
  • Several methods of inserting duplicate key values ​​in MYSQL

<<:  Summary of knowledge points on using calculated properties in Vue

>>:  Detailed explanation of the difference between a href=# and a href=javascript:void(0)

Recommend

MySQL query redundant indexes and unused index operations

MySQL 5.7 and above versions provide direct query...

How to configure Linux CentOS to run scripts regularly

Many times we want the server to run a script reg...

JS Canvas interface and animation effects

Table of contents Overview Canvas API: Drawing Gr...

Vue Element front-end application development dynamic menu and routing association processing

Table of contents Overview 1. Menu and routing pr...

A complete record of a Mysql deadlock troubleshooting process

Preface The database deadlocks I encountered befo...

About the difference between inspecting elements and viewing the source code of a web page

I don’t know if you have noticed that when we ope...

Detailed tutorial on installing MySQL database on Alibaba Cloud Server

Table of contents Preface 1. Uninstall MySQL 2. I...

Vue.js front-end web page pop-up asynchronous behavior example analysis

Table of contents 1. Preface 2. Find two pop-up c...

Tutorial on how to modify element.style inline styles

Preface When we were writing the web page style a...

Mysql view the maximum number of connections and modify the maximum number of connections

MySQL View the maximum number of connections and ...

Detailed explanation of MySQL transactions and MySQL logs

Transactional Characteristics 1. Atomicity: After...

The difference between div and span in HTML (commonalities and differences)

Common points: The DIV tag and SPAN tag treat som...

MySQL Workbench download and use tutorial detailed explanation

1. Download MySQL Workbench Workbench is a graphi...

JS implements the dragging and placeholder functions of elements

This blog post is about a difficulty encountered ...

Example of how to exit the loop in Array.forEach in js

Table of contents forEach() Method How to jump ou...