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

This paper illustrates the usage and difference of 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 designed to solve one of our usual problems.

That is, if the record exists in the database, the data in the record is updated, and if not, the record is added.

Let's 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 'DEFAULT' 'COMMENT' address', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert some data into the table

INSERT INTO testVALUES (NULL, 'averse,' aaa'), (NULL, 'baked,' bbb'), (NULL, 'centering,' ccc'), (NULL, 'dumped,' ddd')

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

We run the following statement:

REPLACE INTO test VALUES (NULL, 'eBay,' eee')

The results show that the number of rows affected is 1, and the record is inserted successfully.

Notice 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 the record with an ID of 1 with 2 rows affected has been updated successfully.

The reason for this 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, obviously this record cannot be inserted successfully, and 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,name field. Let's see if the content of the addr field still exists after replace into.

Obviously, the content of the addr field is gone, which is consistent with our above analysis. Reaplce into first deletes the record with an id of 1, and then inserts the record, but we do not specify the value of addr, so it will be like 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 will be retained instead of the addr field data shown above.

You need to use insert into on duplicate key update here.

Execute the following statement:

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

VALUES (field name) means to get the column value of the current statement insert, and VALUES (name) means' bb''.

The results show that there are 2 rows affected.

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

The practice of the insert into on duplicate key update statement is to insert the record first, and if it is not successful, update the record, but why is the number of rows affected is 2?

Let's build a new 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 'DEFAULT' 'COMMENT' address', PRIMARY KEY (`id`), UNIQUE KEY `sn` (`sn`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert point data into it.

INSERT INTO test2VALUES (NULL, '01century,' asides, 'aaa'), (NULL,' 02, 'baked,' bbb'), (NULL,'03, 'cations,' ccc'), (NULL,'04, 'dudes,' 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 you run the above statement, although the number of affected rows is 0, the self-increment field of the table test2 is incremented by 1.

Obviously, if the insert into on duplicate key update statement only updates based on the original record, the self-increment field will not be automatically added by 1, indicating that it has also performed a record deletion operation.

Insert the record first, and if not, delete the original record, but retain the value of the field except the update statement, then merge the retained value with the value that needs to be updated, and then insert a new record.

Summary:

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

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

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.