Is that query okay? Behavior of the REPLACE clause that you will lose if you are not aware of it

Hello. I'm Mandai, in charge of Wild on the development team.
This time, I would like to follow the detailed behavior of the REPLACE clause, which I (maybe) use casually.

 

How are you updating the data?

Our company uses MySQL as the main RDBMS for system development.
Normally, when updating a table with a unique constraint,

• SELECT, check for duplicates, then INSERT, UPDATE, or branch
• Wild update that tries INSERT and Duplicate, catches the exception and sends it to UPDATE processing

You may be forced to choose between the following, but actually (although it's not a big deal) there is also update processing using the REPLACE clause.

As for this REPLACE clause, if you execute it thinking that it is simply replacing (=REPLACE) as it sounds, data inconsistency may occur and unexpected situations may occur.

Let's look at its behavior with a simple example.

Let's see the movement in action

First, create a table like the one below.

mysql> CREATE TABLE `users` (`id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(16) NOT NULL UNIQUE, `level` INT UNSIGNED NOT NULL DEFAULT 1); Query OK, 0 rows affected (0.02 sec ) mysql> DESC `users`; +-------+-------------------+------+----+- --------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-- ------------------+------+----+---------+---------- ------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(16) | NO | UNI | NULL | | | level | int(10) unsigned | NO | | 1 | | +--------+-----+------+-----+---- ----+----------------+ 3 rows in set (0.00 sec)

The name field is set to a unique key.
Next, let's insert some simple dummy data.

This time, I tried generating dummy data using generatedata.com

I think the atmosphere will be something like the following.

mysql> INSERT INTO `users` (`name`,`level`) VALUES ("Pamela",8),("Dexter",4),("Ava",1),("Kelly",8),( "Leila",2),("Quin",8),("Salvador",8),("Orli",2),("Ignacia",9),("Brennan",8); Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM `users`; +----+----------+-------+ | id | name | level | +----+----------+-------+ | 1 | Pamela | 8 | | 2 | Dexter | 4 | | 3 | Ava 1 | 4 | Kelly | 8 | | 5 | Leila | 2 | | 6 | Quin | 8 | | 7 | Salvador | 8 | | 8 | Orli | 2 | | 9 | Ignacia | 9 | | 10 | Brennan | 8 | +----+----------+-------+ 10 rows in set (0.00 sec)

Now, I would like to insert records with duplicate names.

mysql> INSERT INTO `users` (`name`,`level`) VALUES ("Pamela",1); ERROR 1062 (23000): Duplicate entry 'Pamela' for key 'name' mysql> SELECT * FROM `users`; +----+----------+-------+ | id | name | level | +----+----------+- ------+ | 1 | Pamela | 8 | | 2 | Dexter | 4 | | 3 | Ava | 1 | | 4 | Kelly | 8 | | 5 | Leila | 2 | | 6 | Quin | 8 | | 7 | Salvador | 8 | | 8 | Orli | 2 | | 9 | Ignacia | 9 | | 10 | Brennan | 8 | +----+----------+------ -+ 10 rows in set (0.00 sec)

An error occurred because it was Duplicated.
That's as expected.

Normally, you would use the UPDATE clause here to update the record.

mysql> UPDATE `users` SET `level`=10 WHERE `name` = "Pamela"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from users; +- ---+----------+-------+ | id | name | level | +----+----------+--- ----+ | 1 | Pamela | 10 | | 2 | Dexter | 4 | | 3 | Ava | 1 | | 4 | Kelly | 8 | | 5 | Leila | 2 | | 6 | Quin | 8 | | Salvador | 8 | | 8 | Orli | 2 | | 9 | Ignacia | 9 | | 10 | Brennan | 8 | +----+----------+-------+ 10 rows in set (0.00 sec)

Next, let's use the REPLACE clause to update the record.

mysql> REPLACE INTO `users` (`name`,`level`) VALUES ("Pamela",1); Query OK, 2 rows affected (0.00 sec) mysql> SELECT * FROM `users`; +----+ ----------+-------+ | id | name | level | +----+----------+------- + | 2 | Dexter | 4 | | 3 | Ava | 1 | | 4 | Kelly | 8 | | 5 | Leila | 2 | | 6 | Quin | 8 | | 7 | Salvador | 8 | | 8 | Orli | | 9 | Ignacia | 9 | | 10 | Brennan | 8 | | 12 | Pamela | 1 | +----+----------+-------+ 10 rows in set (0.00 sec)

Did you realize at this point that something terrible had happened?
Yes, for some reason, the primary key of the record with name = "Pamela" has changed!
I would like to take a closer look at this fact, which is as scary as Junji Inagawa's ghost stories.

The REPLACE clause is not just a replacement

This fact is clear from the experiment mentioned earlier, but why did this happen?
By the way, the reason id 11 is missing is because the INSERT clause was executed, and this is not the content of this ghost story.

To put it simply, the reason why this happens is

REPLACE clause "INSERTs a record with the same data" after DELETE

This is because it moves like this.
It's scary.
It's scary to have your primary key change on your own.
If you thought it was a convenient query that automatically determined whether it was an INSERT or an UPDATE, and you used it repeatedly
, you just didn't step on a landmine, and before you know it, you might be in a sea of ​​landmines.

However, this phenomenon occurs because

• has a primary key that is autoincremented
• There are fields that are also unique keys.
• When a unique key is duplicated

limited to.
When I write it like this, some people seem to say it's because I add a surrogate key, but I think the correct answer is that they didn't implement it after knowing the behavior of the REPLACE clause.

There is a syntax that works similarly, called the "INSERT ... ON DUPLICATE KEY UPDATE syntax," but

it is
unsafe to use for statement-based replication or for tables with composite primary (unique) keys. Therefore, I think its usage is limited. Also, many framework O/R mappers are not supported as standard (because they are dialects of MySQL, not standard SQL), so if you use them, you won't benefit from the O/R mapper.
However, it is said that the ON DUPLICATE KEY UPDATE syntax can be processed much faster than SELECT → INSERT (UPDATE), so
if you are using a MySQL system and data update processing is taking a long time, try using It might be a good idea to try using it.

MySQL 5.6 series Japanese document
MySQL 5.7 series document (English)
(The content is roughly the same in the English version of the 5.7 series document)

Also, for the time being, INSERT will cause the auto-increment to count up unnecessarily, which
may lead to the tragedy of running out of primary keys.
If you want to be sure, I think it's a good idea to do SELECT → INSERT (UPDATE).

Well, generatedata.com is really useful.