Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.