MySQL extended feature-repeat insert

Replace into why not? First Delete, after the episode, delete when the full table scan it?

Refer to the documentation from the MySQL official network:

Http://dev.mysql.com/doc/refman/5.0/en/replace.html

MySQL uses the following algorithm for REPLACE (and LOAD DATA ... REPLACE ):

1. Try to insert the new row into the table

2. While the insertion fails because a duplicate-key error occurs for a primary key or unique index:

1. Delete from the table the conflicting row, has the duplicate key value

2. Try again to insert the new row into the table

You can see that replace into will try the two-step action:

1. Try inserting the data into the table. This time, if there are no duplicates of the duplicate key, commit the transaction and end the replace into operation.

2. If a duplicate Insert exception occurs, delete the data row with the duplicate values before attempting to insert the data

From the above step, when the primary key is the Auto_increment field, such a detection is not possible to achieve the purpose.

However, there are cases in which:

When the primary key in a table is a self-growing field, and there is a unique constraint, using replace causes the result:

CREATE TABLE T1 (c1 int NOT NULL Auto_increment primary KEY,C2 int. NOT NULL UNIQUE,C3 varchar (20));

Replace into T1 (C2,C3) VALUES (1, ' 2 ');

Replace into T1 (C2,C3) VALUES (1, ' 2 ');

?

You will find that the primary key value of the self-growing field is not the same.

At this point, this problem does not occur when you use the INSERT INTO on duplicate update clause.

MySQL extended feature-repeat insert