SQL replace into usage and implementation statements

This article combines a large number of articles related to SQL replace into and focuses their usage and advantages in this article. If you need some commands, you can take a closer look.

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

1. Try to Insert a new row into the table

2. When insertion fails due to a duplicate keyword error for the primary key or unique Keyword:

A. Delete conflicting rows with duplicate keyword values from the table

B. Try to Insert a new row into the table again

 

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 the primary key

Test Method 1: insert the same index value:

The Code is as follows:	Copy code
Replace into fanwe_order (id, sn) VALUES ('33', 'test replace into use') Result: Number of affected rows: 2 SELECT * FROM fanwe_order WHERE sn = 'test replace into use'

Result: a row of records is found.

Test Method 2: The primary key value is inserted repeatedly:

Specify the insert id as 34. In this way, it does not overlap with the id in the data table and then runs the query

Replace into fanwe_order (id, sn) VALUES ('34', 'test replace into use ')

Result: no new data is inserted. The original line is replaced. Id changed from 33 to 34

Cause analysis:
As mentioned in the Manual, if an old record in the table has the same value as a new record used for the primary key or a UNIQUE index.

Meaning, unless the table has a primary key or UNIQUE index, it makes no sense to use a REPLACE statement.
In the test example just now. Id is the primary key and sn is the unique index. The test method is the same as the primary key value, and the test method is the unique cable.

The value is the same. Replace occurs in both cases.

Understanding: when inserting data, if the primary key value or unique index key value is the same. In this case, replace (the meaning of the replace word reflects its function) is used to delete the original one. Replace with the currently inserted row (so you must have both the insert and delete permissions)

Delete and insert a new one. It Exactly shows what I see: it shows that the number of affected rows is 2

In either case, replace is used only when the replace statement is used. 1. The primary key value is the same. 2. The index key value is the same.

 

To use REPLACE, you must have both the INSERT and DELETE permissions for the table.

The REPLACE statement returns a number to indicate the number of affected rows. This is the sum of the number of deleted and inserted rows. If this number is 1 for a single row, one row is inserted and no row is deleted. If the number is greater than 1, one or more old rows are deleted before the new row is inserted. If the table contains multiple unique indexes, and the new row copies the values of different old rows in different unique indexes, it is possible that a single row replaces multiple old rows.

The number of affected rows can be easily determined whether REPLACE only adds one row, or whether REPLACE also replaces other rows: Check whether the number is 1 (Added) or larger (replaced ).

MySQL replace into usage (enhanced version of insert)

REPLACE depends on the primary key or UNIQUE index in the table. If the record in a table has the same value as the new record used for the primary key or UNIQUE index, before the new record is inserted, the old record is deleted.
Relying on the primary key or index Mysql can achieve quick judgment. Using REPLACE requires the table's INSERT and DELETE permissions at the same time.

Replace first tries to Insert a new row into the table. If the insertion fails due to a conflict between the primary key and the unique key, the conflicting rows containing duplicate key values will be deleted from the table, then try to insert the new row into the table.

 

In SQL Server, you can perform the following operations:

The Code is as follows:	Copy code
If not exists (select 1 from t where id = 1) Insert T (id, update_time) values (1, getdate ()) Else Update t set update_time = getdate () where id = 1

So how does MySQL implement this logic? Don't worry! MySQL has a simpler method: replace

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 ();

Replace into is similar to insert. The difference is that replace into first tries to insert data into the table. if this row of data already exists in the table (determined based on the primary key or unique index), delete the row of data and insert new data. 2. Otherwise, insert new data directly.