MySQL ON DUPLICATE KEY - 最后插入的ID？

Question

我有以下查询：

INSERT INTO table (a) VALUES (0)
  ON DUPLICATE KEY UPDATE a=1

我希望获取插入或更新后的ID。通常情况下，我需要运行第二个查询以获取它，因为我认为 insert_id() 只能返回“插入”的ID，而不是更新后的ID。

是否有一种方法可以在插入/更新时一次性检索该行的ID，而无需运行两个查询？

- thekevinscott

---

为什么要假设呢？为什么不自己进行测试呢？上面编辑中的 SQL 是能够运行的，而我通过测试发现它比插入失败后捕获、使用 INSERT IGNORE，或者先查询是否存在重复条目这些方法都更加快速。请尝试一下。 - Michael Fenwick

警告：所提出的解决方案是有效的，但是即使没有插入，auto_increment值仍然会增加。如果重复键经常发生，您可能希望在上述查询之后运行alter table tablename AUTO_INCREMENT = 0;，以避免ID值中出现大的空隙。 - Frank Forte

@FrankForte 你是在开玩笑吧，关于在生产环境中运行时使用alter table，还要考虑并发用户的情况？ - doug65536

Answer 1

请查看此页面：https://web.archive.org/web/20150329004325/https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
在页面底部，它们解释了如何通过向该MySQL函数传递表达式来使LAST_INSERT_ID对于更新有意义。

从MySQL文档示例中可以看出：

如果一张表包含一个AUTO_INCREMENT列并且INSERT...UPDATE插入一行，则LAST_INSERT_ID()函数返回AUTO_INCREMENT值。 如果该语句更新一行，则LAST_INSERT_ID()没有意义。 但是，您可以通过使用LAST_INSERT_ID(expr)绕过此问题。 假设id是AUTO_INCREMENT列。 要使LAST_INSERT_ID()对于更新有意义，请按以下方式插入行:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

- fredrik

---

在查看那个页面时，我不知道怎么错过了这一点。因此，更新部分显示为：UPDATE id=LAST_INSERT_ID(id)这很好用。谢谢！ - thekevinscott

据说php函数mysql_insert_id()在以下两种情况下都会返回正确的值：http://www.php.net/manual/en/function.mysql-insert-id.php#59718。 - jayarjo

@PetrPeller - 不看MySQL内部结构，它可能意味着它会产生一个值，但是该值与您刚刚运行的查询无关。换句话说，这是一个很难调试的问题。 - Jason

据说在5.1.12版本之后已不再需要这样做，但今天我找到一个例外。如果您有一个自动递增主键和一个唯一键，例如电子邮件地址，并且“on duplicate update”基于电子邮件地址触发更新，请注意'last_insert_id'将不是已更新行的自动递增值，而似乎是最近插入的自动递增值。这会造成很大的影响。解决方法与此处显示的相同，即在更新查询中使用id = LAST_INSERT_ID(id)。 - sckd

现在引用部分已经改变，对于文档中的内容：如果表包含一个AUTO_INCREMENT列，并且INSERT ... ON DUPLICATE KEY UPDATE插入或更新一行，则LAST_INSERT_ID()函数将返回AUTO_INCREMENT值。 - Erenor Paz

Answer 2

确切地说，如果这是原始查询：

INSERT INTO table (a) VALUES (0)
 ON DUPLICATE KEY UPDATE a=1

如果'id'是自增主键，那么这将是可行的解决方案:

INSERT INTO table (a) VALUES (0)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), a=1

在这里都有：http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

如果表中包含一个AUTO_INCREMENT列，并且INSERT...UPDATE插入一行，则LAST_INSERT_ID()函数返回AUTO_INCREMENT值。 如果语句更新一行，那么LAST_INSERT_ID()就没有意义了。 但是，您可以通过使用LAST_INSERT_ID(expr)来解决此问题。 假设id是AUTO_INCREMENT列。

- Aleksandar Popovic

---

是的，参见被接受的答案，它与你所说的相同。无需重新激活三年前的帖子。不管怎样，感谢您的努力。 - fancyPants

@tombom，我发表这个答案的唯一原因是因为被接受的答案是不正确的——如果没有要更新的内容，它将无法工作。 - Aleksandar Popovic

Answer 3

你可以考虑使用REPLACE，它本质上是删除/插入已有记录。但如果存在自增字段，这将改变其值，并可能破坏与其他数据的关系。

- Brent Baisley

---

啊，是的 - 我正在寻找一些不会删除先前ID的东西。 - thekevinscott

这可能也很危险，因为这也可能会通过约束条件导致另一个相关数据被删除。 - Serge

Answer 4

我不知道您使用的MySQL版本，但在InnoDB中，autoinc存在一个bug。

5.1.20版本中存在该bug，但在5.1.23版本中已得到修复。 http://bugs.mysql.com/bug.php?id=27405 5.1.31版本中同样存在该bug，但在5.1.33版本中也已得到修复。 http://bugs.mysql.com/bug.php?id=42714

- Hugues Van Landeghem

Answer 5

我遇到了一个问题，当使用 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id) 时，会将主键增加1。因此，会使得下一个输入的id在当前会话中增加2。

- Oleksii Zymovets

Answer 6

如果您使用自增，现有的解决方案可以工作。但是我遇到了一个情况，用户可以定义一个前缀，并且序列应该从3000重新开始。由于这个不同的前缀，我不能使用自增，这导致插入时last_insert_id为空。我通过以下方式解决了这个问题：

INSERT INTO seq_table (prefix, id) VALUES ('$user_prefix', LAST_INSERT_ID(3000)) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id + 1);
SELECT LAST_INSERT_ID();

如果前缀存在，则会将其递增并填充last_insert_id。 如果前缀不存在，则会插入值为3000的前缀，并使用3000填充last_insert_id。

- Aaron

Answer 7

值得注意的是，这可能很明显（但是为了清晰起见，我还是要说一下），REPLACE会在插入新数据之前删除现有匹配行。而ON DUPLICATE KEY UPDATE仅更新您指定的列并保留行。

来自手册：

REPLACE的工作方式与INSERT完全相同， 只是如果表中的旧行具有与新行相同的值 PRIMARY KEY或UNIQUE索引，则旧行将被删除， 然后插入新行。

- Greg K