MySQL ON DUPLICATE KEY - 最后插入ID？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/778534/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL ON DUPLICATE KEY - last insert id?
提问by thekevinscott
I have the following query:
我有以下查询：
INSERT INTO table (a) VALUES (0)
ON DUPLICATE KEY UPDATE a=1
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。通常我会运行第二个查询以获得这个，因为我相信 insert_id() 只返回“插入”的 ID 而不是更新的 ID。
Is there a way to INSERT/UPDATE and retrieve the ID of the row without running two queries?
有没有办法在不运行两个查询的情况下插入/更新并检索行的 ID？
回答by fredrik
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.
查看此页面：https: //web.archive.org/web/20150329004325/https: //dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
在页面底部他们解释了如何通过将表达式传递给该 MySQL 函数来使 LAST_INSERT_ID 对更新有意义。
From the MySQL documentation example:
来自 MySQL 文档示例：
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:
如果表包含 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;
回答by Aleksandar Popovic
To be exact, if this is the the original query:
确切地说，如果这是原始查询：
INSERT INTO table (a) VALUES (0)
ON DUPLICATE KEY UPDATE a=1
and 'id' is the auto-increment primary key than this would be the working solution:
并且 'id' 是自动递增的主键，而不是工作解决方案：
INSERT INTO table (a) VALUES (0)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), a=1
Is all here: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
都在这里：http: //dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
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.
如果表包含 AUTO_INCREMENT 列并且 INSERT ... UPDATE 插入一行，则 LAST_INSERT_ID() 函数返回 AUTO_INCREMENT 值。如果语句改为更新一行，则 LAST_INSERT_ID() 没有意义。但是，您可以使用 LAST_INSERT_ID(expr) 解决此问题。假设 id 是 AUTO_INCREMENT 列。
回答by Brent Baisley
You might look at REPLACE, which is essentially a delete/insert if the record exists. But this would change the auto increment field if present, which could break relationships with other data.
您可能会查看 REPLACE，如果记录存在，它本质上是删除/插入。但这会更改自动增量字段（如果存在），这可能会破坏与其他数据的关系。
回答by Hugues Van Landeghem
I don't know what is your version of MySQL but with InnoDB, there was bug with autoinc
我不知道您的 MySQL 版本是什么，但是使用 InnoDB，autoinc 存在错误
bug in 5.1.20 and corrected in 5.1.23 http://bugs.mysql.com/bug.php?id=27405
5.1.20 中的错误并在 5.1.23 中更正 http://bugs.mysql.com/bug.php?id=27405
bug in 5.1.31 and corrected in 5.1.33 http://bugs.mysql.com/bug.php?id=42714
5.1.31 中的错误并在 5.1.33 中更正 http://bugs.mysql.com/bug.php?id=42714
回答by Oleksii Zymovets
I have come across a problem, when ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id) increment the primary key by 1. So the id of the next input within the session will be incremented by 2
我遇到了一个问题，当 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id) 将主键增加 1 时。因此会话中下一个输入的 id 将增加 2
回答by Aaron
The existing solutions work if you use autoincrement. I have a situation where the user can define a prefix and it should restart the sequence at 3000. Because of this varied prefix, I cannot use autoincrement, which makes last_insert_id empty for inserts. I solved it with the following:
如果您使用自动增量，则现有解决方案有效。我有一种情况，用户可以定义一个前缀，它应该在 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();
If the prefix exists, it will increment it and populate last_insert_id. If the prefix does not exist, it will insert the prefix with the value 3000 and populate last_insert_id with 3000.
如果前缀存在，它将增加它并填充 last_insert_id。如果前缀不存在，它将插入值为 3000 的前缀，并用 3000 填充 last_insert_id。
回答by Greg K
It's worth noting, and this might be obvious (but I'll say it anyway for clarity here), that REPLACE will blow away the existing matching row before inserting your new data. ON DUPLICATE KEY UPDATE will only update the columns you specify and preserves the row.
值得注意的是，这可能很明显（但为了清楚起见，我还是会在这里说），REPLACE 将在插入新数据之前清除现有的匹配行。ON DUPLICATE KEY UPDATE 只会更新您指定的列并保留该行。
From the manual:
从手册：
REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
REPLACE 的工作方式与 INSERT 完全相同，但如果表中的旧行与 PRIMARY KEY 或 UNIQUE 索引的新行具有相同的值，则在插入新行之前删除旧行。
