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