这个查询可以吗？ 如果您不知道的话，您将丢失 REPLACE 子句的行为

你好。我是Mandai，负责Wild 开发团队。
这次，我想了解 REPLACE 子句的详细行为，我（也许）会随意使用它。

 

您如何更新数据？

我们公司使用MySQL作为主要的RDBMS进行系统开发。
通常，当更新具有唯一约束的表时，

• 选择，检查重复项，然后插入、更新或分支
• 尝试 INSERT 和 Duplicate 的疯狂更新，捕获异常并将其发送到 UPDATE 处理

您可能被迫在以下选项之间进行选择，但实际上（尽管这不是什么大问题）还存在使用 REPLACE 子句的更新处理。

对于这个REPLACE子句，如果你认为它只是简单地替换(=REPLACE)，那么执行它时，可能会出现数据不一致的情况，并可能出现意想不到的情况。

让我们用一个简单的例子来看看它的行为。

让我们看看实际行动

首先，创建一个如下所示的表。

mysql> CREATE TABLE `users` (`id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCRMENT, `name` VARCHAR(16) NOT NULL UNIQUE, `level` INT UNSIGNED NOT NULL DEFAULT 1); 查询正常，0 行受影响（0.02 秒）； ) mysql> DESC `用户`; +--------+--------------------+------+----+- --------+----------------+ | 类型 | 额外 | ------------------+------+----+---------+--------- - ------+ | int(10) | NULL | varchar(16) | 空 | | 1 | +--------+-----+-----+-----+----- ---------+ 3 行一组（0.00 秒）

名称字段设置为唯一键。
接下来，让我们插入一些简单的虚拟数据。

这次，generateddata.com生成虚拟数据

我想气氛会是这样的。

mysql> 插入 `users` (`name`,`level`) 值 ("Pamela",8),("Dexter",4),("Ava",1),("Kelly",8),( "Leila",2),("Quin",8),("Salvador",8),("Orli",2),("Ignacia",9),("Brennan",8); 查询OK,受影响的 10 行（0.00 秒）记录：10 重复项：0 警告：0 mysql> SELECT * FROM `users`; +----+----------+--------+ | 姓名 | +----+------+ | 8 | 1 | 4 | 2 | 8 | 伊格纳西亚 | +----+----------+--------+ 10 行一组（0.00 秒）

现在，我想插入具有重复名称的记录。

mysql> INSERT INTO `users` (`name`,`level`) VALUES ("Pamela",1); 错误 1062 (23000): 重复条目 'Pamela' 为键 'name' mysql> SELECT * FROM `users`; +----+----------+-----+ | 名称 | +----+----------+- ------+ | 2 | 4 | 5 | 7 | 8 | 9 | 布伦南 | -+ 10 行一组（0.00 秒）

由于重复而发生错误。
这正如预期的那样。

通常，您可以在此处使用 UPDATE 子句来更新记录。

mysql> UPDATE `users` SET `level`=10 WHERE `name` = "Pamela"; 查询正常，1 行受影响（0.00 秒）匹配的行：1 已更改：1 警告：0 mysql> select * from users; ---+----------+-----+ | 名称 | +----+----------+--- ----+ | 10 | 4 | 凯利 | 6 8 | 8 | 9 | 8 | +----+----------+已设置（0.00 秒）

接下来，让我们使用 REPLACE 子句来更新记录。

mysql> REPLACE INTO `users` (`name`,`level`) VALUES ("Pamela",1); 查询正常，2 行受影响（0.00 秒） mysql> SELECT * FROM `users`; ----------+--------+ | 名称 | +----+----------+----- + | 4 | 4 | 雷拉 | 8 | | 9 | 布伦南 | 1 | +----+----------+秒）

此时你是否意识到发生了可怕的事情？
是的，由于某种原因，name =“Pamela”的记录的主键已更改！
我想仔细看看这个事实，它就像稻川润二的鬼故事一样可怕。

REPLACE 子句不仅仅是替换

这个事实从前面提到的实验中已经很清楚了，但是为什么会出现这种情况呢？
顺便说一句，id 11缺失的原因是因为执行了INSERT子句，而这不是这个鬼故事的内容。

简单来说，出现这种情况的原因是

DELETE 之后的 REPLACE 子句“插入具有相同数据的记录”

这是因为它像这样移动。
太可怕了。
自行更改主键是很可怕的。
如果你认为这是一个方便的查询，自动判断是INSERT还是UPDATE，并且你反复使用它
，那么你只是没有踩到地雷，在你意识到之前，你可能已经陷入了一片汪洋大海地雷。

然而，出现这种现象的原因是

• 有一个自动递增的主键
• 有些字段也是唯一键。
• 当唯一键重复时

仅限于。
当我这样写时，有些人似乎说这是因为我添加了代理键，但我认为正确的答案是他们在知道 REPLACE 子句的行为后没有实现它。

有一种类似的语法，称为“INSERT ... ON DUPLICATE KEY UPDATE 语法”，但

用于基于语句的复制或具有复合主（唯一）键的表是不安全的，
此外，许多框架 O/R 映射器不作为标准支持（因为它们是 MySQL 的方言，而不是标准 SQL），因此如果您使用它们，您将不会从 O/R 映射器中受益。
不过，据说 ON DUPLICATE KEY UPDATE 语法的处理速度比 SELECT → INSERT (UPDATE) 快得多，因此
如果您使用的是 MySQL 系统并且数据更新处理需要很长时间，尝试使用它可能会是一个不错的选择尝试使用它的想法。

MySQL 5.6系列日文文档
MySQL 5.7系列文档（英文）
（内容与英文版5.7系列文档大致相同）

另外，暂时INSERT会导致自增不必要的计数，
可能会导致主键耗尽的悲剧。
如果你想确定的话，我认为执行 SELECT → INSERT (UPDATE) 是个好主意。

嗯， generatedata.com确实很有用。