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

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

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.

 

 

 

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly give the link at the beginning of the article. Welcome to discussion