如何在MySQL中获取最后更新行的ID？

Question

如何使用 PHP 获取 MySQL 中最后更新行的 ID？

- Avinash

---

mysqli_insert_id($link)会准确返回最后更新行的id。我在我的项目中使用这个函数来达到同样的目的。你可以在同步或异步查询中都使用它。只需将$lastupdated_row_id = mysqli_insert_id($link)插入到你的代码中，它就能为你工作。 - Naeem Ul Wahhab

如果你更新，难道不已经知道行的ID了吗？我猜可能有一些情况你不知道。 - jlcharette

你可能在update查询中使用了where子句，那么为什么不能在select查询中使用相同的where子句呢？UPDATE foo WHERE bar = 1; SELECT id FROM foo WHERE bar = 1？ - Salman A

Answer 1

我已经找到了解决这个问题的答案 :)

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;

编辑 by aefxx

这种技术可以进一步扩展，以检索由更新语句影响的每行的ID：

SET @uids := null;
UPDATE footable
   SET foo = 'bar'
 WHERE fooid > 5
   AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
SELECT @uids;

这将返回一个由所有ID用逗号连接的字符串。

- Pomyk

---

难以置信这个帖子没有获得任何点赞，而那个与 OP 问题毫不相关的 mysql_insert_id() 评论却有13个赞。感谢 Pomyk，这是一个聪明的技巧！ - Jaka Jančar

也许大家都懂这个，但如果你使用mysql_query（）函数的话，你需要将其拆分为三个不同的调用，但它仍然能够正常工作。 - user254875486

我将代码的第一行更改为 SET @uids := ''; 以使其正常工作，否则 SELECT @uids; 的结果将是 blob。 - Hendry H.

不需要使用变量。LAST_INSERT_ID()可以接受参数，该参数将设置下一次调用LAST_INSERT_ID()时返回的值。例如：UPDATE table SET id=LAST_INSERT_ID(id), row='value' WHERE other_row='blah';。现在，SELECT LAST_INSERT_ID();将返回更新后的ID。有关更多详细信息，请参见newtover的答案。 - Joel

@SteveChilds，我只是在评论newtover的问题，并想为您描述的陷阱添加一个解决方案。如果没有要更新的内容，则将LAST_INSERT_ID设置为0：UPDATE lastinsertid_test SET row='value' WHERE row='asd' AND LAST_INSERT_ID(id) OR LAST_INSERT_ID(0);。但是，它无法检索多个ID，因此此答案更为优秀。 - martinczerwi

Answer 2

哦，我很惊讶在答案中没有看到最简单的解决方案。

假设item_id是items表中的整数自增列，并且您使用以下语句更新行：

UPDATE items
SET qwe = 'qwe'
WHERE asd = 'asd';

那么，如果想要在这条语句执行之后知道最新受影响的行，你需要略微修改这个语句：

UPDATE items
SET qwe = 'qwe',
    item_id=LAST_INSERT_ID(item_id)
WHERE asd = 'asd';
SELECT LAST_INSERT_ID();

如果您需要仅更新真正更改的行，则需要通过LAST_INSERT_ID添加对item_id的条件更新，以检查数据是否将更改在该行中。

- newtover

---

多用户安全性好，因为多个客户端可以发出UPDATE语句并使用SELECT语句（或mysql_insert_id()）获取自己的序列值，而不会影响其他生成自己序列值的客户端，也不会被其他客户端所影响。请参考https://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id。 - brutuscat

这样做会将item_id设置为最后插入的记录吗？ - arleslie

@arleslie，如果你按照brutuscat在评论中提供的文档链接，你会发现它不会。这正是预期的行为，完全符合此情况。 - newtover

这确实是正确的答案。虽然查询似乎有点违反直觉，但这正是MySQL文档所建议的做法。 - Timothy Zorn

如果这个查询让您感到困惑，因为LAST_INSERT_ID（expr）将返回UPDATE调用的expr的值，您也可以像这样使用它：UPDATE items SET qwe ='qwe' WHERE asd ='asd' AND LAST_INSERT_ID(item_id) ; SELECT LAST_INSERT_ID();。 - martinczerwi

Answer 3

这个过程官方上看起来很简单，但实际上却是相当反直觉的。如果你正在进行以下操作：

update users set status = 'processing' where status = 'pending'
limit 1

将其更改为：

update users set status = 'processing' where status = 'pending'
and last_insert_id(user_id) 
limit 1

last_insert_id(user_id)在where子句中的添加是告诉MySQL将其内部变量设置为找到行的ID。当您像这样向last_insert_id(expr)传递一个值时，它最终返回该值，在这种情况下，ID始终为正整数，因此始终评估为true，不会干扰where子句。只有在实际找到一些行时才起作用，因此记得检查受影响的行数。然后可以以多种方式获取ID。

MySQL last_insert_id()

您可以在不调用LAST_INSERT_ID()的情况下生成序列，但是使用此函数的效用在于ID值作为最后自动生成的值在服务器中保持。它是多用户安全的，因为多个客户端可以发出UPDATE语句，并使用SELECT语句（或mysql_insert_id()）获得自己的序列值，而不会受到其他生成自己序列值的客户端的影响或受到影响。

MySQL mysql_insert_id()

返回上一个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列发生的情况的更准确的信息。

PHP mysqli_insert_id()

使用LAST_INSERT_ID()函数执行INSERT或UPDATE语句还将修改mysqli_insert_id()函数返回的值。

综合起来：

$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，DB class在这里。）

- Vlad Kornea

---

需要注意的是：这是多连接安全的，last_insert_id或mysql_insert_id（也许包括mysqli_insert_id，我没有检查）的持久化值是针对每个连接的。太棒了！ - Ryan S

这看起来是一个不错的解决方案，但引入了一个我仍然不理解的错误 - 在处理多个条目时，有时会跳过返回的ID数量，这与原始查询的派生合并行为不同（在更新语句中选择语句）。我不得不回滚并改为使用两个不同的查询。 - Lucas Bustamante

@LucasBustamante，你的环境是什么？node-mysql2连接池会破坏事务、临时表和last_insert_id()。解决方法是从池中提取连接，在同一连接上运行连续查询，然后将连接释放回池中。 - Val Kornea

@RyanS 每个连接还是每个会话？ - TheRealChx101

@TheRealChx101 哦天啊，这已经是5年前的事了，抱歉:D 我想我当时正在阅读这份文档：https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id 它说“每个连接”和“每个客户端”，除此之外，我不太确定，很抱歉！ - Ryan S

Answer 4

这是与Salman A的答案相同的方法，但这里是您实际需要执行的代码。

首先，编辑您的表格，使其能够自动跟踪每当行被修改。如果您只想知道何时插入了一行，请删除最后一行。

ALTER TABLE mytable
ADD lastmodified TIMESTAMP 
    DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP;

然后，要找到最后更新的行，您可以使用此代码。

SELECT id FROM mytable ORDER BY lastmodified DESC LIMIT 1;

这段代码是从MySQL与PostgreSQL：向表中添加“最后修改时间”列和MySQL手册：排序行中获取的。我只是将它们组合起来。

- Chad von Nau

---

如果第二个插入查询在第一个插入查询之后立即执行，那么该方法可能会获取错误的id。但是，如果我们在这个查询中使用'WHERE'，即SELECT id FROM mytable ORDER BY lastmodified DESC LIMIT 1 WHERE（与最后一个插入查询相关的某些条件），则可以防止获取错误的id。 - Naeem Ul Wahhab

@TheNoble-Coder 这是正确的。如果您需要获取特定更新查询所影响的行的ID，则使用Pomyk的技术。当异步使用时，此技术更加有用，您只需要获取绝对最后的更新。 - Chad von Nau

Answer 5

查询：

$sqlQuery = "UPDATE 
            update_table 
        SET 
            set_name = 'value' 
        WHERE 
            where_name = 'name'
        LIMIT 1;";

PHP 函数:

function updateAndGetId($sqlQuery)
{
    mysql_query(str_replace("SET", "SET id = LAST_INSERT_ID(id),", $sqlQuery));
    return mysql_insert_id();
}

这对我很有效 ;)

- Gigoland

---

我喜欢同样的东西，但运行方式不同。参考：http://forums.mysql.com/read.php?52,390616,390619 - user1642018

Answer 6

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;

我不知道为什么必须要将id转换一下（CAST）才能获取@uids的内容（它是一个二进制大对象）。 顺便说一下，非常感谢Pomyk的回答！

- Gromish

Answer 7

我的解决方案是，首先通过select命令确定“id”（@uids），然后使用@uids更新此id。

SET @uids := (SELECT id FROM table WHERE some = 0 LIMIT 1);
UPDATE table SET col = 1 WHERE id = @uids;SELECT @uids;

它在我的项目上运行良好。

- Ahmet Uğur

Answer 8

嘿，我刚好需要这样一个技巧——我用了另一种方法解决了它，也许对你有用。请注意，这不是可扩展的解决方案，并且对于大型数据集来说非常糟糕。

将查询分为两个部分 -

首先，选择要更新的行的ID，并将它们存储在临时表中。

其次，在更新语句中将条件更改为where id in temp_table进行原始更新。

为确保并发性，在这两个步骤之前需要锁定表，然后在结束时释放锁定。

同样地，这对于以limit 1结尾的查询适用于我，因此我甚至不使用临时表，而是简单地使用变量来存储第一个select的结果。

我更喜欢这种方法，因为我知道我只会更新一行，而且代码很直观。

- olamundo

Answer 9

最后更新的行的ID与您在“updateQuery”中使用以查找和更新该行的ID相同。因此，只需以任何方式保存（调用）该ID即可。

last_insert_id() 取决于 AUTO_INCREMENT，但最后更新的ID则不是。

- Driada

---

如果更新不是基于ID而是其他一组属性，会怎样？ - Sebas

Answer 10

进一步说明上述已接受的答案

对于那些想知道:=和=的区别的人

:=和=之间有显著的区别，:=作为变量赋值运算符可以在任何地方使用，而=仅在SET语句中以这种方式工作，并且在其他任何地方都是比较运算符。

因此，SELECT @var = 1 + 1;将不会改变@var的值并返回一个布尔值(1或0，取决于@var的当前值)，而SELECT @var := 1 + 1;将把@var更改为2，并返回2。 [来源]

- Anon30

---

嘿，谢谢。他们的意思实际上比上面被接受的答案更有趣。 - Green