MySQL handles the solution of the duplicate value of the primary key unique key in the insert process _mysql

This article mainly introduces in inserting the data to the table to encounter the key duplication avoids inserting the duplicate value the processing method, mainly relates to Ignore,on DUPLICATE key update,replace; then look at the three ways to deal with each.

IGNORE

Using ignore automatically ignores duplicate record rows when the inserted value encounters a primary key (PRIMARY key) or a unique key (unique key) repeats, and does not affect the insertion of subsequent rows of records.

To create a test table

CREATE TABLE tignore
(ID int not NULL PRIMARY KEY,
NAME1 int

Normal Insert If there is a key repeat in the record that is inserted, the entire statement will fail

Using ignore the record row that has duplicate values in the inserted record does not affect the insertion of other rows.

REPLACE

Use replace Deletes a duplicate row of records in a table and then inserts when the inserted record encounters a primary key or a unique key repeats.

To create a test table

DROP TABLE IF EXISTS treplace;
CREATE TABLE treplace
(ID int not NULL PRIMARY KEY,
NAME1 int

From the output of the information can be seen is 4 rows affected, indicating that it was first inserted (1,1) and then deleted (1,1)

On DUPLICATE KEY UPDATE

When the inserted record encounters a primary key or a unique key repeats, the update operation defined later is performed.

This is equivalent to performing an insert operation and then performing an update operation based on a primary key or a unique key.

To create a 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;

The first statement is equivalent to executing:

INSERT into Tupdate () VALUES (1,1)
UPDATE tupdate
SET name1=name1+1

The second statement is equivalent to executing:

INSERT into Tupdate () VALUES (1,1)
UPDATE tupdate
SET name1=2+1

Using values after the on DUPLICATE KEY update refers to the value of the inserted record, and not using values refers to the table's own value.

Note: It is important that the record of the update update that is executed after the on DUPLICATE key update is the ID of the where duplicate primary key or unique key.

For example, the following situation:

It is a unique key NAME1 repeat but the primary key does not repeat, the executed statement is this:

INSERT into Tupdate () VALUES (1,1)
UPDATE tupdate
SET name1=2+1

Do not think that inserts the primary key id=2 the record in.

Summarize

The above three methods for handling duplicate values support the standard Insert syntax, including INSERT INTO ... VALUES, INSERT into .... SET, INSERT into ... SELECT.

About MySQL handling the primary key unique key duplicate value in the Insert Process Small series to introduce so many people, I hope to help you!