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

Detailed explanation of the mechanism and implementation of accept lock in Nginx

Preface nginx uses a multi-process model. When a ...

Vue implements partial refresh of the page (router-view page refresh)

Using provide+inject combination in Vue First you...

Detailed explanation of Vue's monitoring method case

Monitoring method in Vue watch Notice Name: You s...

Detailed example of sharedWorker in JavaScript to achieve multi-page communication

This is what happened. Today I was playing with G...

40 CSS/JS style and functional technical processing

1- Styling dropdown select boxes - Modify the dro...

Regarding the problem of using webpack instructions in vscode showing "Because running scripts is prohibited in this system" (perfect solution)

When learning Vue, when I always use webpack inst...

Comprehensive analysis of optimistic locking, pessimistic locking and MVCC in MySQL

Preface In the actual use of the database, we oft...

Two ways to implement text stroke in CSS3 (summary)

question Recently I encountered a requirement to ...

Detailed explanation of the my.ini Chinese configuration scheme for MySql optimization: InnoDB, 4GB memory, and multiple queries

This article is a MySQL configuration file soluti...

Example of using @media responsive CSS to adapt to various screens

Definition and Use Using @media queries, you can ...

Implementation of master-slave replication in docker compose deployment

Table of contents Configuration parsing Service C...

How to quickly deploy an Elasticsearch cluster using docker

This article will use Docker containers (orchestr...

Basic learning tutorial of table tag in HTML

Table label composition The table in HTML is comp...

Detailed analysis of the parameter file my.cnf of MySQL in Ubuntu

Preface Based on my understanding of MySQL, I thi...

CentOS6.8 uses cmake to install MySQL5.7.18

Referring to the online information, I used cmake...