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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.