Usage and difference analysis of replace into and insert into on duplicate key update in MySQL

This article uses examples to illustrate the usage and differences between replace into and insert into on duplicate key update in MySQL. Share with you for your reference, the details are as follows:

Both replace into and insert into on duplicate key update are to solve a problem we usually have

That is, if the record exists in the database, update the data in the record; if not, add the record.

We create a test table test

CREATE TABLE `test` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `name` varchar(32) DEFAULT '' COMMENT 'Name',
 `addr` varchar(256) DEFAULT '' COMMENT 'Address',
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert some data into the table

INSERT INTO test
VALUES
	(NULL, 'a', 'aaa'),
	(NULL, 'b', 'bbb'),
	(NULL, 'c', 'ccc'),
	(NULL, 'd', 'ddd');

The number of affected rows is 4, and the results are as follows:

We run the following statement:

REPLACE INTO test VALUES(NULL, 'e', ​​'eee');

The result shows that 1 row is affected and the record is inserted successfully.

Note that in the above statement, we did not fill in the primary key ID.

Then we execute the following statement:

REPLACE INTO test VALUES(1, 'aa', 'aaaa');

The results show that 2 rows are affected and the record with ID 1 is updated successfully.

Why does this happen? The reason is that replace into will first try to insert a record into the table. Because our ID is the primary key and cannot be repeated, this record obviously cannot be inserted successfully. Then replace into will delete the existing record and then insert it, so it will show that the number of affected rows is 2.

Let's run the following statement again:

REPLACE INTO test(id,name) VALUES(1, 'aaa');

Here we only specify the id and name fields. Let's see if the addr field content still exists after replace into.

Obviously, the content of the addr field is gone, which is consistent with our analysis above. reaplce into first deletes the record with id 1, and then inserts the record, but we did not specify the value of addr, so it will be as shown in the figure above.

But sometimes our requirement is to update the data of the specified field if the record exists, and the original field data is still retained, instead of the addr field data being gone as shown above.

Here you need to use insert into on duplicate key update

Execute the following statement:

INSERT INTO test (id, name)
VALUES(2, 'bb') 
ON DUPLICATE KEY 
UPDATE 
name = VALUES(name);

VALUES(field name) means getting the column value of the current statement insert, VALUES(name) means 'bb'

The results show that 2 rows are affected

As shown in the figure above, the value of the addr field is retained.

The insert into on duplicate key update statement inserts the record first, and if it fails, updates the record, but why is the number of rows affected 2?

Let's rebuild a table test2

CREATE TABLE `test2` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `sn` varchar(32) DEFAULT '' COMMENT 'Unique key',
 `name` varchar(32) DEFAULT '' COMMENT 'Name',
 `addr` varchar(256) DEFAULT '' COMMENT 'Address',
 PRIMARY KEY (`id`),
 UNIQUE KEY `sn` (`sn`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert some data into it

INSERT INTO test2
VALUES
	(NULL, '01', 'a', 'aaa'),
	(NULL, '02', 'b', 'bbb'),
	(NULL, '03', 'c', 'ccc'),
	(NULL, '04', 'd', 'ddd');

We run the following statement:

INSERT INTO test2 (sn, name, addr)
VALUES
	('02', 'bb', 'bbbb')
ON DUPLICATE KEY 
UPDATE 
name = VALUES(name),
addr = VALUES(addr);

The results are as follows:

Each time the above statement is run, although the number of affected rows is 0, the auto-increment field of table test2 is increased by 1.

Obviously, if the insert into on duplicate key update statement only updates the original record, the auto-increment field will not be automatically increased by 1, which means that it also performs a record deletion operation.

Insert the record first. If it fails, delete the original record, but retain the value of the field after the update statement. Then merge the retained value with the value to be updated, and then insert a new record.

Summarize:

Both replace into and insert into on duplicate key update try to insert the record first. If it fails, the record is deleted. replace into does not retain the value of the original record, while insert into on duplicate key update does. Then insert a new record.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.