SQL replace into usage and implementation statement

The following is a more detailed description of the algorithm used (the algorithm is also used for load DATA ...). REPLACE):

1. Try inserting the new row into the table

2. When an insert fails because of a duplicate keyword error for a primary key or unique keyword:

A. Delete conflicting rows that contain duplicate key values from the table

B. Try inserting the new row into the table again

Use format:

The code is as follows	Copy Code
REPLACE [Low_priority | Delayed] [Into] tbl_name [(Col_name,...)] VALUES ({expr | DEFAULT},...), (...),... Or: REPLACE [Low_priority | Delayed] [Into] Tbl_name SET col_name={expr | DEFAULT}, ... Or: REPLACE [Low_priority | Delayed] [Into] tbl_name [(Col_name,...)] SELECT ...

ID is primary key

Test mode One, insert index values are the same:

The code is as follows	Copy Code
REPLACE into Fanwe_order (ID,SN) VALUES (' 33′, ' test replace into use ') Result: number of rows affected: 2 SELECT * from Fanwe_order WHERE sn= ' test replace into use '

Result: A line of records was detected

Test mode Two, insert primary key value is duplicate:

Specify the Insert ID as 34. This does not duplicate the ID in the datasheet and then run the query

Replace into Fanwe_order (ID,SN) VALUES (' 34′, ' test replace into use ')

Result: There is no new insertion of a single piece of data. or replaced the original line. ID changed from 33 to 34

Reason Analysis:
The manual mentions that if an old record in the table has the same value as a new record for primary key or a unique index.

In other words, it is meaningless to use a Replace statement unless the table has a primary key or a unique index.
In the example just tested. The ID is the primary key and the SN is the unique index. Test mode one is the same as the primary key value, test mode two is the emergence of a unique cable

The same as the citation. Replace is present in both cases

Understanding: When inserting data, if you encounter a primary key value or a unique index key value. Then use the alternative (the meaning of the replace word to react to its effect) by deleting the original. Replace with the currently inserted row (so you need both insert and delete permissions)

Delete First and insert new. Just shows what I'm seeing: the number of rows affected is 2.

One of two cases replaces with the Replace statement: 1. The primary key value is the same 2. Index key values are the same

In order to be able to use replace, you must have both insert and delete permissions for the table.

The Replace statement returns a number that indicates the number of rows affected. This number is the same as the number of rows that were deleted and inserted. If the number is 1 for a single line replace, the row is inserted and no rows are deleted. If the number is greater than 1, one or more old rows are deleted before the new row is inserted. If a table contains more than one unique index, and a new row duplicates a value from a different old row in a different unique index, it is possible that a single row replaces more than one old row.

The number of rows affected can easily determine whether replace has added only one row, or if replace has also replaced other rows: check to see if the number is 1 (added) or larger (replace).

MySQL Replace into usage (enhanced version of INSERT INTO)

Replace depends on the primary key or unique index in the table, and if the record exists in one table has the same value as the new record for the primary key or unique index, the old record is deleted before the new record is inserted.
Depending on the primary key or indexed MySQL can make fast judgments, and with replace, you need to have both insert and delete permissions for the table.

Replace first attempts to insert a new row into the table, and if the insertion fails because of a conflict between the primary key or the unique key, deletes the conflicting row containing the duplicate key value from the table, and then attempts to insert the new row into the table.

This can be done in SQL Server:

The code is as follows	Copy Code
If not exists (select 1 from t where id = 1) INSERT INTO T (ID, Update_time) VALUES (1, GETDATE ()) Else Update T Set update_time = GETDATE () Where id = 1

So how does MySQL implement such a logic? Don't worry! There is a simpler way to do this in MySQL: replace into

The code is as follows	Copy Code
Replace Into T (ID, Update_time) VALUES (1, now ());

Or

The code is as follows	Copy Code
Replace Into T (ID, update_time) Select 1, now ();

The replace into is similar to the Insert feature, except that replace into first attempts to insert data into the table, 1. If you find that this row of data is already in the table (judged by a primary key or a unique index), the row data is deleted first, and then the new data is inserted. 2. Otherwise, insert the new data directly.