The true difference between Mysql's replace into and insert into on duplicate key update _mysql

Let's look at the following example:

1 Replace into ...
1.1 Input raw Data
mysql> use test;
Database changed
Mysql>

mysql> CREATE TABLE T1 SELECT 1 as a, ' C3 ' as B, ' C2 ' as C;
ALTER TABLE T1 Change a a INT PRIMARY KEY auto_increment;
Query OK, 1 row affected (0.03 sec)
Records:1 duplicates:0 warnings:0

mysql> INSERT into T1 SELECT 2, ' 2 ', ' 3 ';
Query OK, 1 row affected (0.01 sec)
Records:1 duplicates:0 warnings:0
mysql> INSERT INTO T1 (b,c) Select ' R2 ', ' R3 ';
Query OK, 1 row affected (0.08 sec)
Records:1 duplicates:0 warnings:0

1.2 Start Replace operation
Mysql> REPLACE into T1 (a,b) VALUES (2, ' a ');
Query OK, 2 rows affected (0.06 sec)

"" See here, replace, see here, the C field in A=2 's record is empty string,
So when it conflicts with the key, replace overwrites the related field, and the other fields fill the default value, which can be understood as deleting the duplicate key record, inserting a new record, a delete original record, and then insert the Operation .

1.3 But do not know the auto_increment of the primary key has no effect, and then test:

mysql> INSERT INTO T1 (b,c) Select ' R4 ', ' R5 '; 
Query OK, 1 row affected (0.05 sec) 
records:1 duplicates:0 warnings:0 mysql> 

select * from T1; 
+---+----+----+ 
| a | b 
| +---+----+----+ 
| 1 | c3 | c2 | 
| 2 | A | | | 
3 | R2 | R3 | 
| 5 | R4 | R5 | 
+---+----+----+ 
4 rows in Set (0.00 sec)

"From here we can see that the new self increase is not starting from 4, but starting from 5, which means a repalce operation, the auto_increment in the primary key will add up to 1."
So the summary is as follows:
Replace:

When there is no key, replace is equivalent to a normal insert.
When there is a key, can be understood as the deletion of duplicate key records, in the case of keeping key unchanged, delete the original record, and then insert a new record, the value will only input the value of the field in the Replace statement, the rest is not in the Replace statement field, will automatically fill the default value.

2.1 OK, and then see INSERT INTO ... on duplicate key update,

mysql> INSERT INTO T1 (a,b) Select ' 3 ', ' R5 ' on duplicate key update b= ' R5 '; 
Query OK, 2 rows affected, 1 Warning (0.19 sec) 
records:1 duplicates:1 warnings:1 mysql> 

select * from t1;
  +---+----+----+ 
| a | b 
| +---+----+----+ 
| 1 | c3 | c2 | 
| 2 | A | | | 
3 | R5 | R3 | 
| 5 | R4 | R5 | 
+---+----+----+ 
4 rows in Set (0.00 sec)

"" A=5 time, the original C value is still in, this means that when the key sometimes, only the following Udate action statements are executed.

2.2 Check the auto_increment condition again.

mysql> INSERT INTO T1 (a,b) Select ' 3 ', ' R5 ' on duplicate key update b= ' R5 '; 
Query OK, 2 rows affected, 1 Warning (0.19 sec) 
records:1 duplicates:1 warnings:1 mysql> 

select * from t1;
  +---+----+----+ 
| a | b 
| +---+----+----+ 
| 1 | c3 | c2 | 
| 2 | A | | | 
3 | R5 | R3 | 
| 5 | R4 | R5 | 
+---+----+----+ 
4 rows in Set (0.00 sec) 

mysql> inserts into T1 (b,c) Select ' R6 ', ' R7 '; 
Query OK, 1 row affected (0.19 sec) 
records:1 duplicates:0 warnings:0 mysql> 

select * from T1; 
+---+----+----+ 
| a | b 
| +---+----+----+ 
| 1 | c3 | c2 | 
| 2 | A | | | 
3 | R5 | R3 | 
| 5 | R4 | R5 | 
| 7 | R6 | R7 | 
+---+----+----+ 

As you can see from here, the new self increase does not start with 6, but starts at 7, representing an insert. On Deplicate udate operation, the auto_increment in the primary key also accumulates 1 as replace.

2.3 To see when there is no key, insert ... On Deplicate update

mysql> INSERT INTO T1 (a,b,c) Select ' A ', ' R5 ', ' C3 ' on duplicate key update b= ' R5 '; 
Query OK, 1 row affected, 1 warning (0.23 sec) 
records:1 duplicates:0 warnings:1 mysql> 

select * from T1;
  +----+----+----+ 
| a | b 
| +----+----+----+ 
| 1 | c3 | c2 | 
| 2 | A | | | 
3 | B5 | R3 | 
| 5 | R4 | R5 | 
| 7 | R6 | R7 | 
| 9 | S6 | S7 | 
| 33 | R5 | C3 | 
+----+----+----+ 
7 rows in Set (0.00 sec)

Look at the records of a=33, OK, all entered.

3 summary from the above test results see that the same:
(1), when there is no key, replace and insert. On Deplicate udpate the same.
(2), when there is a key, all retain the primary key value, and Auto_increment automatic +1
The difference: When there is key, replace is delete old records, and input new records, so the original records will be cleared, this time, if the replacement statement of the field is not full, some of the original example of the C field value will be automatically populated as the default value.
and insert. Deplicate update executes only the SQL after the update tag, representing a simple update statement on the surface.
But in fact, according to my conjecture, if it is a simple UPDATE statement, Auto_increment will not be +1, it should be the first delete, and then insert the operation, Only the values of all fields except the fields after the update are preserved during the insert process.

So the difference between the two is only one,insert ... on deplicate udpate preserves the old values of all the fields, overwrites and inserts them together, and replace does not keep the old values and deletes and inserts the new values directly.
In terms of bottom execution efficiency, replace is more than insert. On deplicate update efficiency is high, but when you write replace, the field to write full, to prevent the old field data is deleted.

Personal inclination with replace.