MySQL ON DUPLICATE KEY - 最后插入ID?

问题描述

我有以下查询:

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

我想要插入或更新的 ID.通常我会运行第二个查询以获得这个，因为我相信 insert_id() 只返回插入"的 ID 而不是更新的 ID.

I want the ID of either the insert or the update. Usually I run a second query in order to get this as I believe insert_id() only returns the 'inserted' ID and not the updated ID.

有没有办法在不运行两个查询的情况下插入/更新并检索行的 ID?

Is there a way to INSERT/UPDATE and retrieve the ID of the row without running two queries?

推荐答案

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

Check this page out: https://web.archive.org/web/20150329004325/https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
At the bottom of the page they explain how you can make LAST_INSERT_ID meaningful for updates by passing an expression to that MySQL function.

来自 MySQL 文档示例:

From the MySQL documentation example:

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

If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

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

这篇关于MySQL ON DUPLICATE KEY - 最后插入ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！