MySQL LAST_INSERT_ID 查询

Question

我看过 official documentation但我还是有点困惑。

假设我有一个程序，它运行并执行插入，然后我请求 LAST_INSERT_ID()，我是从我的程序实例运行的插入中获取最后一个插入 ID 还是它是调用该过程的任何实例/ session 在表中最后插入的 ID 吗？

例如，假设最后插入的记录 ID 是 4，我调用了该过程，我的插入将是 ID 5，但是我的插入失败了，我会得到 4 作为最后插入 ID 返回还是 null/0 值？

Answer 1

非常link您在问题中提供的文档页面有答案:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

因此，不存在来自其他客户端的竞争条件。您需要在与 INSERT 相同的连接中请求 LAST_INSERT_ID() 以获得正确的结果。

至于事务回滚时会发生什么，未定义:

If the previous statement returned an error, the value of LAST_INSERT_ID() is undefined. For transactional tables, if the statement is rolled back due to an error, the value of LAST_INSERT_ID() is left undefined. For manual ROLLBACK, the value of LAST_INSERT_ID() is not restored to that before the transaction; it remains as it was at the point of the ROLLBACK.