Detailed explanation of avoiding the cheating operation of MySQL replacing logic SQL

The difference between replace into and insert into on duplicate key

Usage of replace

insert when not conflicted, remaining column defaults
When key conflicts, self-added columns are updated, replace conflicts columns, and other columns are defaulted
Com_replace adds 1
Innodb_rows_updated adds 1

Usage of Insert into … on duplicate key

insert without conflict, default value of other columns
In case of conflict with key, only the corresponding field value of update.
Com_insert adds 1
Innodb_rows_inserted increases by 1

Experimental demonstration

Table structure

create table helei1(
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL DEFAULT '',
age tinyint(3) unsigned NOT NULL default 0,
PRIMARY KEY(id),
UNIQUE KEY uk_name (name)
)
ENGINE=innodb AUTO_INCREMENT=1 
DEFAULT CHARSET=utf8;

Table data

root@127.0.0.1 (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 |  He Lei  | 26 |
| 2 |  Xiao Ming  | 28 |
| 3 |  Xiao Hong  | 26 |
+----+-----------+-----+
3 rows in set (0.00 sec)

Usage of replace into

root@127.0.0.1 (helei)> replace into helei1 (name) values(' He Lei ');
Query OK, 2 rows affected (0.00 sec)
root@127.0.0.1 (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 2 |  Xiao Ming  | 28 |
| 3 |  Xiao Hong  | 26 |
| 4 |  He Lei  | 0 |
+----+-----------+-----+
3 rows in set (0.00 sec)
root@127.0.0.1 (helei)> replace into helei1 (name) values(' Ai Xuan ');
Query OK, 1 row affected (0.00 sec)
root@127.0.0.1 (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 2 |  Xiao Ming  | 28 |
| 3 |  Xiao Hong  | 26 |
| 4 |  He Lei  | 0 |
| 5 |  Ai Xuan  | 0 |
+----+-----------+-----+
4 rows in set (0.00 sec)

Usage of replace

When there is no key conflict, replace into is equivalent to insert, and the remaining columns default

When key conflicts, self-increment column updates, replace conflicts column, and other columns default

Insert into … on duplicate key:

root@127.0.0.1 (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 2 |  Xiao Ming  | 28 |
| 3 |  Xiao Hong  | 26 |
| 4 |  He Lei  | 0 |
| 5 |  Ai Xuan  | 0 |
+----+-----------+-----+
4 rows in set (0.00 sec)
root@127.0.0.1 (helei)> insert into helei1 (name,age) values(' He Lei ',0) on duplicate key update age=100;
Query OK, 2 rows affected (0.00 sec)
root@127.0.0.1 (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 2 |  Xiao Ming  | 28 |
| 3 |  Xiao Hong  | 26 |
| 4 |  He Lei  | 100 |
| 5 |  Ai Xuan  | 0 |
+----+-----------+-----+
4 rows in set (0.00 sec)
root@127.0.0.1 (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 2 |  Xiao Ming  | 28 |
| 3 |  Xiao Hong  | 26 |
| 4 |  He Lei  | 100 |
| 5 |  Ai Xuan  | 0 |
+----+-----------+-----+
4 rows in set (0.00 sec)
root@127.0.0.1 (helei)> insert into helei1 (name) values(' Ai Xuan ') on duplicate key update age=120;
Query OK, 2 rows affected (0.01 sec)
root@127.0.0.1 (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 2 |  Xiao Ming  | 28 |
| 3 |  Xiao Hong  | 26 |
| 4 |  He Lei  | 100 |
| 5 |  Ai Xuan  | 120 |
+----+-----------+-----+
4 rows in set (0.00 sec)
root@127.0.0.1 (helei)> insert into helei1 (name) values(' Nonexistent ') on duplicate key update age=80;
Query OK, 1 row affected (0.00 sec)
root@127.0.0.1 (helei)> select * from helei1;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 2 |  Xiao Ming  | 28 |
| 3 |  Xiao Hong  | 26 |
| 4 |  He Lei  | 100 |
| 5 |  Ai Xuan  | 120 |
| 8 |  Nonexistent  | 0 |
+----+-----------+-----+
5 rows in set (0.00 sec)

Summarize

The usage of replace into is equivalent to doing one delete operation first and then one insert operation if a conflicting key is found, and the unspecified columns use default values. This situation will lead to changes in the self-increasing primary key. If there are foreign keys in the table or the business logically depends on the primary key, an exception will occur. Therefore, it is recommended to use Insert into … on duplicate key. Because the writing time is also very hasty, it is inevitable that there will be some mistakes or inaccuracies in the text. Please criticize and correct the inappropriate places.