这个查询语句可以吗？我追踪了 REPLACE 子句的行为，你应该了解一下。

2016.02.23

Web系统开发

0.1 如何更新数据？
0.2 让我们看看它的实际表现。
0.3 REPLACE 条款不仅仅是替换。

1 如果您想咨询开发专业人士

大家好，我是开发团队的通配符专家 Mandai。
这次，我想更深入地了解一下 REPLACE 子句的详细行为，您可能在不知不觉中就使用它了。

如何更新数据？

我们公司主要使用 MySQL 作为关系数据库管理系统 (RDBMS) 进行系统开发。
通常情况下，当更新带有唯一约束的表时……

SELECT 并检查重复项，然后分支执行 INSERT 或 UPDATE 操作。
如果尝试插入数据时发现重复项，则捕获异常并继续进行更新处理。

你可能被迫在上述两个选项中做出选择，但实际上（也不完全是），还可以使用 REPLACE 子句进行更新。

如果您认为 REPLACE 子句只是像字面意思那样替换数据，那么执行此子句可能会导致数据不一致，并导致意想不到的情况。

我们来看一个简单的例子，看看它是如何运作的。

让我们看看它的实际表现。

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

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 行数据 (0.00 秒)

`name` 字段被设置为唯一键。
接下来，我们将插入一些简单的示例数据。

这次，generatedata.com我尝试使用

我认为现场气氛大概会是这样的：

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); 查询成功，影响 10 行 (0.00 秒) 记录数：10 重复数：0 警告数：0 mysql> SELECT * FROM `users`; | Quin | 8 | | 7 | Salvador | 8 | | 8 | Orli | 2 | | 9 | Ignacia | 9 | | 10 | Brennan | 8 | +----+----------+-------+ 10 行结果 (0.00 秒)

现在，我们来尝试插入一条名称重复的记录。

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 | 10 rows in set (0.00 sec)

由于数据重复，所以发生了错误。
这正是我所预料的。

通常情况下，你会在这里使用 UPDATE 子句来更新记录。

mysql> UPDATE `users` SET `level`=10 WHERE `name` = "Pamela"; 查询成功，影响 1 行 (0.00 秒) 匹配行数：1 更改行数：1 警告数：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 | in set (0.00 秒)

现在让我们尝试使用 REPLACE 子句更新记录。

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

你现在意识到发生了什么可怕的事情了吗？
没错，不知何故，名称为“Pamela”的记录的主键被更改了！
让我们仔细看看这个事实，它就像稻川纯二的鬼故事一样恐怖。

REPLACE 条款不仅仅是替换。

前面提到的实验已经证实了这一点，但为什么会发生这种情况呢？
顺便一提，ID 11 缺失的原因是执行了 INSERT 语句，但这并非本文的重点。

简而言之，原因在于：

REPLACE 子句“在 DELETE 操作后插入一条具有相同数据的记录”。

因为它就是这样运作的。
是
不是很可怕？主键居然会自行更改，这确实很可怕。
如果你一直频繁使用它，认为它是一个方便的查询，能够自动判断是插入还是更新，那么你
刚刚躲过了一颗地雷，但很快你可能就会陷入一片地雷之中。

然而，这种现象发生在

具有自动递增的主键
同时，还有一个字段是唯一键。
当唯一键重复时

仅限于[特定情况]。
有人可能会说这是因为使用了代理键，但我认为正确的解释是，这是因为实现过程中并没有完全理解 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 英文文档内容基本相同）

因为它会不必要地递增自增键值
可能会导致主键耗尽，
为了安全起见，SELECT → INSERT（UPDATE）似乎是更可靠的方法。

哇，generatedata.com真方便。