How to avoid the pit of MySQL replacing logical SQL
This article is about how to avoid MySQL replacing logical SQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.
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 0Magnum 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 | Xiaohong | 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 | 8 | does not exist | 0 | +-+ 5 rows in set (0.00 sec) Thank you for your reading! This is the end of this article on "how to avoid the pit of MySQL replacing logical SQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!
