How does MySQL solve the unique primary key duplicate value during the insertion process? mysql primary key

How does MySQL solve the unique primary key duplicate value during the insertion process? mysql primary key

This article describes how to avoid DUPLICATE keys when inserting data into a table. It mainly involves IGNORE, on duplicate key update, and REPLACE; next, let's take a look at the three solutions.

IGNORE

Ignore is used to automatically ignore duplicate record rows when the inserted value encounters a primary key or a unique key (unique key) Repeat, without affecting the insertion of subsequent record rows,

Create test table

CREATE TABLE Tignore(ID INT NOT NULL PRIMARY KEY ,NAME1 INT)default charset=utf8; 

Normal insertion: If the inserted record contains duplicate keys, an error is reported, and the entire statement fails to be executed.

If you use IGNORE to insert a record with a duplicate value, this record row with the duplicate value will be ignored without affecting the insertion of other rows.

REPLACE

Use replace to delete duplicate record rows in the table before inserting a record when the primary key or unique key is repeated.

REPLACE INTO Treplace() VALUES(1,1),(1,2),(2,2); 

Create test table

DROP TABLE IF EXISTS Treplace;CREATE TABLE Treplace(ID INT NOT NULL PRIMARY KEY ,NAME1 INT)default charset=utf8; 

The output information shows that four rows are affected, indicating that () is inserted first and then () is deleted)

ON DUPLICATE KEY UPDATE

When the inserted record encounters a duplicate primary key or unique key, the subsequent UPDATE operation is performed.

It is equivalent to executing the Insert operation first, and then performing the update operation based on the primary key or unique key.

Create test table

DROP TABLE IF EXISTS Tupdate;CREATE TABLE Tupdate(ID INT NOT NULL PRIMARY KEY ,NAME1 INT UNIQUE KEY)default charset=utf8; INSERT INTO Tupdate() VALUES(1,1),(1,2) ON DUPLICATE KEY UPDATE NAME1=NAME1+1;INSERT INTO Tupdate() VALUES(1,1),(1,2) ON DUPLICATE KEY UPDATE NAME1=VALUES(NAME1)+1; 

The first statement is equivalent to execution:

INSERT INTO Tupdate() VALUES(1,1)UPDATE TupdateSET NAME1=NAME1+1WHERE ID=1; 

The second statement is equivalent to execution:

INSERT INTO Tupdate() VALUES(1,1)UPDATE TupdateSET NAME1=2+1WHERE ID=1; 

After the on duplicate key update, VALUES indicates the value of the inserted record, instead of the value of the table.

Note: The UPDATE record executed after the on duplicate key update is the ID of the WHERE primary KEY or unique KEY, which is very important.

For example:

INSERT INTO Tupdate() VALUES(1,1),(2,1) ON DUPLICATE KEY UPDATE NAME1=VALUES(ID)+1; 

The unique key NAME1 is repeated, but the primary key is not repeated. The execution statement is as follows:

INSERT INTO Tupdate() VALUES(1,1)UPDATE TupdateSET NAME1=2+1WHERE ID=1; 

Do not insert a record with primary key ID = 2.

Summary

The above three methods for processing repeated VALUES all support the standard INSERT syntax, including insert into... VALUES, insert into... SET, insert into... SELECT.

The solution to the unique primary key duplicate values in the Insert Process of MySQL is described in this article. I hope it will help you!

Articles you may be interested in:

• In-depth exploration of the causes of primary key duplication caused by mysql auto-increment Columns