REPLACE INTO，它会重用主键吗？

Question

在MySQL中，REPLACE INTO函数的作用是删除并插入行。在我的表中，主键(id)是自增的，因此我期望它会删除然后插入一个带有id在数据库尾部的表。

然而，它插入时却用相同的id！这是预期的行为吗？还是我有什么遗漏了？（我在调用REPLACE INTO语句时没有设置id）

- matt

---

无法重现：CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL UNIQUE, value VARCHAR(200) NOT NULL); REPLACE INTO test (name, value) VALUES ('foo'，'bar'); REPLACE INTO test (name, value) VALUES ('foo'，'baz'); SELECT id FROM test; 返回结果为2，而不是1 - 似乎 id 已经被增加了两次。（编辑：在InnoDB和MyISAM下进行测试） - lanzz

你在这个表上使用的是哪种存储引擎？ - Mike Brant

您观察到的行为与预期的行为不一致。可能的解释之一是DELETE操作未成功，可能是由于外键约束违规引起的。这是我能想到的唯一解释。如果没有更实质性的测试用例来演示观察到的行为，我无法提供更多帮助。 - spencer7593

观察到这种行为的另一个解释是一个有问题的 BEFORE INSERT 触发器正在给 id 列分配一个值。但这非常不寻常。 - spencer7593

这简直是疯狂的。我确实想要 OP 所说的行为，即在替换语句中保留相同的 id 而不是更新它！！ - Spencer Williams

Answer 1

如果您的表中还有另一个UNIQUE索引，那么这是一种预期的行为，否则它会像您期望的那样添加该行。请参阅文档：

REPLACE与INSERT完全相同，只是如果表中的旧行具有与PRIMARY KEY或UNIQUE索引的新行相同的值，则在插入新行之前删除旧行。请参见第13.2.5节“INSERT语法”。

https://dev.mysql.com/doc/refman/5.5/en/replace.html

这真的很有道理，因为否则MySQL如何找到要替换的行呢？它只能扫描整个表，那将耗费很长时间。我创建了一个SQL Fiddle来演示这一点，请看此处。

- hol

---

OP指定他没有为“id”列提供值。如果语句为“id”列提供了一个值，那么我们期望该值被“重用”。但是如果没有提供值，我们期望正常的AUTO_INCREMENT行为。 - spencer7593

确实，我有另一个像你所描述的UNIQUE索引。非常感谢你的帮助。 :) - matt

Answer 2

这是预期的行为。从技术上讲，在要替换/插入的数据中如果所有唯一键（不仅是主键）与现有行匹配，MySQL实际上会删除您现有的行并插入一个新行，其中使用唯一键的相同值作为替换数据。因此，如果您查看此类查询的受影响行数，每次替换都会得到2个受影响的行数，而直接插入只有1个。

- Mike Brant

---

这并没有真正解释为什么会重复使用ID。 - Robert Harvey

实际上，提问者（正确地）期望他的自增id在替换时被重新分配，但他观察到保留了id。 - lanzz

你有展示这个行为的测试用例吗？你对这个行为的解释与我使用MyISAM和InnoDB时所经历的行为相反。 - spencer7593