MySQL handles the primary key unique key duplicate value method during the Insert process

Introduction

This article mainly introduces the method to avoid inserting duplicate values when inserting data into a table, mainly involves Ignore,on DUPLICATE key update,replace, and then we will take a look at the three ways to deal with them separately.

IGNORE

Use Ignore to automatically ignore duplicate record rows when the inserted value encounters a primary key (PRIMARY key) or a unique key, and does not affect the insertion of subsequent record lines,

Create a test table

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

Normal insert if there are key duplicates in the inserted record, the entire statement will fail

Use Ignore if a duplicate value exists in the inserted record, the record row that ignores the duplicate value does not affect the insertion of the other rows.

REPLACE

Use replace when the inserted record encounters a primary key or a unique key is repeated, delete the duplicate row of records in the table before inserting.

REPLACE into   treplace () VALUES (1,1), (1,2), (2,2);

Create a test table

DROP TABLE  IF EXISTS Treplace; CREATE  TABLE  treplace (ID int not NULL PRIMARY KEY, NAME1 INT)default Charset=utf8;

The information from the output can be seen to be 4 rows affected, indicating that it was first inserted (and then deleted)

On DUPLICATE KEY UPDATE

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

Equivalent to performing an insert operation, and then performing an update operation based on the primary key or unique key.

Create a test table

DROP TABLE  IF EXISTS tupdate; CREATE  TABLE int not NULL PRIMARY KEYint      UNIQUEKEY)default charset=UTF8;

INSERT  intoTupdate ()VALUES(1,1),(1,2) onDUPLICATEKEY UPDATENAME1=NAME1+1;INSERT  intoTupdate ()VALUES(1,1),(1,2) onDUPLICATEKEY UPDATENAME1=VALUES(NAME1)+1;

The first statement is equivalent to executing:

INSERT  into VALUES (1,1) UPDATE tupdate SET NAME1=NAME1+1WHERE ID=1;

The second statement is equivalent to executing:

INSERT  into VALUES (1,1) UPDATE tupdate SET NAME1=2+1WHERE ID=1;

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

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

For example, the following situation:

INSERT  into VALUES (1,1), (2,1onKEYUPDATE NAME1=  VALUES(ID)+1;

It is the only key NAME1 repeated but the primary key is not duplicated, and the statement executed is this:

INSERT  into VALUES (1,1) UPDATE tupdate SET NAME1=2+1WHERE ID=1;

Do not assume that the primary key id=2 will be inserted in the record.

Summary

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

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly give the link. Welcome to the exchange of discussions

MySQL handles the primary key unique key duplicate value method during the Insert process