Avoid the tricky operation of replacing logical SQL by MySQL
The difference between replace into and insert into on duplicate key
The usage of replace
When there is no conflict, it is equivalent to insert, and the rest of the column defaults
When key conflicts, self-increment columns are updated, replace conflicting columns, and other columns default
Com_replace will add 1.
Innodb_rows_updated will add 1.
Insert into... The usage of on duplicate key
When there is no conflict, it is equivalent to insert, and the rest of the column defaults
When a conflict occurs with key, only the corresponding field values are update.
Com_insert will add 1.
Innodb_rows_inserted will increase 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 | Xiaoming | 28 |
| | 3 | Xiao Hong | 26 | |
+-- +
3 rows in set (0.00 sec)
Replace into usage
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 | Xiaoming | 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 ('Aixuan')
Query OK, 1 row affected (0.00 sec)
Root@127.0.0.1 (helei) > select * from helei1
+-- +
| | id | name | age | |
+-- +
| | 2 | Xiaoming | 28 |
| | 3 | Xiao Hong | 26 | |
| | 4 | he Lei | 0 | |
| | 5 | Aixuan | 0 |
+-- +
4 rows in set (0.00 sec)
The usage of replace
When there is no key conflict, replace into is equivalent to insert, and the rest of the column defaults
When key conflicts, self-increment columns are updated, replace conflicting columns, and other columns default
Insert into... On duplicate key:
Root@127.0.0.1 (helei) > select * from helei1
+-- +
| | id | name | age | |
+-- +
| | 2 | Xiaoming | 28 |
| | 3 | Xiao Hong | 26 | |
| | 4 | he Lei | 0 | |
| | 5 | Aixuan | 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 | Xiaoming | 28 |
| | 3 | Xiao Hong | 26 | |
| | 4 | he Lei | 100 | |
| | 5 | Aixuan | 0 |
+-- +
4 rows in set (0.00 sec)
Root@127.0.0.1 (helei) > select * from helei1
+-- +
| | id | name | age | |
+-- +
| | 2 | Xiaoming | 28 |
| | 3 | Xiao Hong | 26 | |
| | 4 | he Lei | 100 | |
| | 5 | Aixuan | 0 |
+-- +
4 rows in set (0.00 sec)
Root@127.0.0.1 (helei) > insert into helei1 (name) values ('Aixuan') on duplicate key update age=120
Query OK, 2 rows affected (0.01sec)
Root@127.0.0.1 (helei) > select * from helei1
+-- +
| | id | name | age | |
+-- +
| | 2 | Xiaoming | 28 |
| | 3 | Xiao Hong | 26 | |
| | 4 | he Lei | 100 | |
| | 5 | Aixuan | 120 | |
+-- +
4 rows in set (0.00 sec)
Root@127.0.0.1 (helei) > insert into helei1 (name) values ('does not exist') 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 | Xiaoming | 28 |
| | 3 | Xiao Hong | 26 | |
| | 4 | he Lei | 100 | |
| | 5 | Aixuan | 120 | |
| | 8 | does not exist | 0 |
+-- +
5 rows in set (0.00 sec)
Summary
The use of replace into is equivalent to if you find a conflict key, first do a delete operation, and then do an insert operation, and the unspecified column uses the default value, which will lead to a change in the self-increasing primary key. If there is a foreign key in the table or the business logically depends on the primary key, then an exception will occur. Therefore, it is recommended to use Insert into... On duplicate key . As the writing time is also very short, it is inevitable that there will be some errors or inaccuracies in the article. I urge readers to criticize and correct them.
Like readers can click like to follow, your praise and attention is the greatest encouragement and support for the author to continue to post!
