Shulou(Shulou.com)06/01 Report--

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!

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.