php 如何获取 MySQL 中最后更新行的 ID？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1388025/
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
How to get ID of the last updated row in MySQL?
提问by Avinash
How do I get the ID of the last updated row in MySQL using PHP?
如何使用 PHP 在 MySQL 中获取最后更新行的 ID？
回答by Pomyk
I've found an answer to this problem :)
我找到了这个问题的答案:)
SET @update_id := 0;
UPDATE some_table SET column_name = 'value', id = (SELECT @update_id := id)
WHERE some_other_column = 'blah' LIMIT 1;
SELECT @update_id;
EDITby aefxx
由 aefxx编辑
This technique can be further expanded to retrieve the ID of every row affected by an update statement:
可以进一步扩展此技术以检索受更新语句影响的每一行的 ID：
SET @uids := null;
UPDATE footable
SET foo = 'bar'
WHERE fooid > 5
AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
SELECT @uids;
This will return a string with all the IDs concatenated by a comma.
这将返回一个字符串，其中所有 ID 用逗号连接。
回答by newtover
Hm, I am surprised that among the answers I do not see the easiest solution.
嗯，我很惊讶在答案中我没有看到最简单的解决方案。
Suppose, item_idis an integer identity column in itemstable and you update rows with the following statement:
假设，item_id是items表中的整数标识列，并且您使用以下语句更新行：
UPDATE items
SET qwe = 'qwe'
WHERE asd = 'asd';
Then, to know the latest affected row right after the statement, you should slightly update the statement into the following:
然后，要在语句之后立即知道受影响的最新行，您应该将语句稍微更新为以下内容：
UPDATE items
SET qwe = 'qwe',
item_id=LAST_INSERT_ID(item_id)
WHERE asd = 'asd';
SELECT LAST_INSERT_ID();
If you need to update only really changed row, you would need to add a conditional update of the item_id through the LAST_INSERT_ID checking if the data is going to change in the row.
如果您只需要更新真正更改的行，则需要通过 LAST_INSERT_ID 检查行中的数据是否要更改来添加 item_id 的条件更新。
回答by Vladimir Kornea
This is officially simple but remarkably counter-intuitive. If you're doing:
这在官方上很简单，但非常违反直觉。如果你在做：
update users set status = 'processing' where status = 'pending'
limit 1
Change it to this:
改成这样：
update users set status = 'processing' where status = 'pending'
and last_insert_id(user_id)
limit 1
The addition of last_insert_id(user_id)in the whereclause is telling MySQL to setits internal variable to the ID of the found row. When you pass a value to last_insert_id(expr)like this, it ends up returning that value, which in the case of IDs like here is always a positive integer and therefore always evaluates to true, never interfering with the where clause. This only works if some row was actually found, so remember to check affected rows. You can then get the ID in multiple ways.
的添加last_insert_id(user_id)中where子句告诉MySQL来设置它的内部变量为所找到的行的ID。当你传递一个值到last_insert_id(expr)like this 时，它最终会返回那个值，在像这里这样的 ID 的情况下，它总是一个正整数，因此总是评估为真，从不干扰 where 子句。这仅在实际找到某行时才有效，因此请记住检查受影响的行。然后，您可以通过多种方式获取 ID。
You can generate sequences without calling LAST_INSERT_ID(), but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value. It is multi-user safe because multiple clients can issue the UPDATE statement and get their own sequence value with the SELECT statement (or mysql_insert_id()), without affecting or being affected by other clients that generate their own sequence values.
您可以在不调用 LAST_INSERT_ID() 的情况下生成序列，但以这种方式使用该函数的效用是 ID 值在服务器中作为最后自动生成的值保留在服务器中。它是多用户安全的，因为多个客户端可以发出 UPDATE 语句并使用 SELECT 语句（或 mysql_insert_id()）获取自己的序列值，而不会影响其他生成自己序列值的客户端或受其影响。
Returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement. Use this function after you have performed an INSERT statement into a table that contains an AUTO_INCREMENT field, or have used INSERT or UPDATE to set a column value with LAST_INSERT_ID(expr).
The reason for the differences between LAST_INSERT_ID() and mysql_insert_id() is that LAST_INSERT_ID() is made easy to use in scripts while mysql_insert_id() tries to provide more exact information about what happens to the AUTO_INCREMENT column.
返回前一个 INSERT 或 UPDATE 语句为 AUTO_INCREMENT 列生成的值。在对包含 AUTO_INCREMENT 字段的表执行 INSERT 语句，或者使用 INSERT 或 UPDATE 设置具有 LAST_INSERT_ID(expr) 的列值后，使用此函数。
LAST_INSERT_ID() 和 mysql_insert_id() 之间差异的原因是 LAST_INSERT_ID() 在脚本中易于使用，而 mysql_insert_id() 试图提供有关 AUTO_INCREMENT 列发生的情况的更准确信息。
Performing an INSERT or UPDATE statement using the LAST_INSERT_ID() function will also modify the value returned by the mysqli_insert_id() function.
使用 LAST_INSERT_ID() 函数执行 INSERT 或 UPDATE 语句也将修改 mysqli_insert_id() 函数返回的值。
Putting it all together:
把它们放在一起：
$affected_rows = DB::getAffectedRows("
update users set status = 'processing'
where status = 'pending' and last_insert_id(user_id)
limit 1"
);
if ($affected_rows) {
$user_id = DB::getInsertId();
}
(FYI that DB class is here.)
（仅供参考，DB 课程在这里。）
回答by Chad von Nau
This is the same method as Salman A's answer, but here's the code you actually need to do it.
这与 Salman A 的答案相同，但这是您实际需要执行的代码。
First, edit your table so that it will automatically keep track of whenever a row is modified. Remove the last line if you only want to know when a row was initially inserted.
首先，编辑您的表格，以便在修改行时自动跟踪。如果您只想知道最初插入行的时间，请删除最后一行。
ALTER TABLE mytable
ADD lastmodified TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;
Then, to find out the last updated row, you can use this code.
然后，要找出最后更新的行，您可以使用此代码。
SELECT id FROM mytable ORDER BY lastmodified DESC LIMIT 1;
This code is all lifted from MySQL vs PostgreSQL: Adding a 'Last Modified Time' Column to a Tableand MySQL Manual: Sorting Rows. I just assembled it.
这段代码全部摘自MySQL vs PostgreSQL: Add a 'Last Modified Time' Column to a Table和MySQL Manual: Sorting Rows。我刚组装好。
回答by GigolNet Guigolachvili
Query :
询问 ：
$sqlQuery = "UPDATE
update_table
SET
set_name = 'value'
WHERE
where_name = 'name'
LIMIT 1;";
PHP function:
PHP函数：
function updateAndGetId($sqlQuery)
{
mysql_query(str_replace("SET", "SET id = LAST_INSERT_ID(id),", $sqlQuery));
return mysql_insert_id();
}
It's work for me ;)
这对我有用;)
回答by Anon30
Further more to the Above Accepted Answer
For those who were wondering about :=& =
进一步了解上述接受的答案
对于那些想知道:=&=
Significant difference between :=and =, and that is that :=works as a variable-assignment operator everywhere, while =only works that way in SET statements, and is a comparison operator everywhere else.
:=and之间的显着区别=，即它:=在任何地方都作为变量赋值运算符=起作用，而仅在 SET 语句中以这种方式起作用，并且在其他任何地方都是比较运算符。
So SELECT @var = 1 + 1;will leave @varunchanged and return a boolean(1 or 0 depending on the current value of @var), while SELECT @var := 1 + 1;will change @varto 2, and return 2.[Source]
SoSELECT @var = 1 + 1;将@var保持不变并返回一个布尔值（1 或 0 取决于@var 的当前值），而SELECT @var := 1 + 1;将更@var改为2，并返回 2。[来源]
回答by Driada
ID of the last updated row is the same ID that you use in the 'updateQuery' to found & update that row. So, just save(call) that ID on anyway you want.
最后更新行的 ID 与您在“updateQuery”中用于查找和更新该行的 ID 相同。因此，只需根据需要保存（调用）该 ID。
last_insert_id()depends of the AUTO_INCREMENT, but the last updated ID not.
last_insert_id()取决于AUTO_INCREMENT，但最后更新的 ID 不是。
回答by olamundo
Hey, I just needed such a trick - I solved it in a different way, maybe it'll work for you. Note this is not a scalable solution and will be very bad for large data sets.
嘿，我只是需要这样一个技巧 - 我用不同的方式解决了它，也许它对你有用。请注意，这不是一个可扩展的解决方案，对于大型数据集来说非常糟糕。
Split your query into two parts -
将您的查询分为两部分 -
first, select the ids of the rows you want to update and store them in a temporary table.
首先，选择要更新的行的 id 并将它们存储在临时表中。
secondly, do the original update with the condition in the update statement changed to where id in temp_table.
其次，将更新语句中的条件更改为 进行原始更新where id in temp_table。
And to ensure concurrency, you need to lockthe table before this two steps and then release the lock at the end.
并且为了保证并发性，需要在这两个步骤之前锁定表，然后在最后释放锁定。
Again, this works for me, for a query which ends with limit 1, so I don't even use a temp table, but instead simply a variable to store the result of the first select.
同样，这对我limit 1有用，对于以 结尾的查询，所以我什至不使用临时表，而只是一个变量来存储第一个select.
I prefer this method since I know I will always update only one row, and the code is straightforward.
我更喜欢这种方法，因为我知道我总是只更新一行，而且代码很简单。
回答by Gromish
SET @uids := "";
UPDATE myf___ingtable
SET id = id
WHERE id < 5
AND ( SELECT @uids := CONCAT_WS(',', CAST(id AS CHAR CHARACTER SET utf8), @uids) );
SELECT @uids;
I had to CAST the id (dunno why)... or I cannot get the @uids content (it was a blob) Btw many thanks for Pomyk answer!
我不得不转换 id（不知道为什么）......或者我无法获得 @uids 内容（它是一个 blob）顺便说一句，非常感谢 Pomyk 的回答！
回答by a1kmm
If you are only doing insertions, and want one from the same session, do as per peirix's answer. If you are doing modifications, you will need to modify your database schema to store which entry was most recently updated.
如果您只进行插入，并且希望在同一会话中插入，请按照 peirix 的回答进行操作。如果您正在进行修改，则需要修改数据库架构以存储最近更新的条目。
If you want the id from the last modification, which may have been from a different session (i.e. not the one that was just done by the PHP code running at present, but one done in response to a different request), you can add a TIMESTAMP column to your table called last_modified (see http://dev.mysql.com/doc/refman/5.1/en/datetime.htmlfor information), and then when you update, set last_modified=CURRENT_TIME.
如果您想要来自上次修改的 id，该 id 可能来自不同的会话（即不是当前运行的 PHP 代码刚刚完成的那个，而是响应不同的请求完成的），您可以添加一个TIMESTAMP 列到名为 last_modified 的表中（有关信息，请参阅http://dev.mysql.com/doc/refman/5.1/en/datetime.html），然后在更新时设置 last_modified=CURRENT_TIME。
Having set this, you can then use a query like: SELECT id FROM table ORDER BY last_modified DESC LIMIT 1; to get the most recently modified row.
设置后，您可以使用如下查询：SELECT id FROM table ORDER BY last_modified DESC LIMIT 1; 获取最近修改的行。
