MySQL 和 PDO:理论上 PDO::lastInsertId 会失败吗?

2021-12-26 00:00:00 database mysql pdo

我一直在考虑这个问题.

I have been pondering on this for a while.

考虑一个庞大的网络应用程序，假设每秒执行数百万个 SQL 查询.

Consider a web application of huge proportions, where, let's say, millions of SQL queries are performed every second.

我运行我的代码:

$q = $db->prepare('INSERT INTO Table
                 (First,Second,Third,Fourth)
          VALUES (?,?,?,?)');
$q->execute(array($first,$second,$third,$fourth));

紧接着，我想获取最后一次查询的自动递增 ID:

Then immediately after, I want to fetch the auto incremented ID of this last query:

$id = $db->lastInsertId();

lastInsertId 是否有可能失败，即获取在我的两个代码块之间 执行的某些 SQL 插入查询的 ID?

Is it possible for lastInsertId to fail, i.e. fetch the ID of some SQL insert query that was executed between my two code blocks?

中学:

如果它会失败，堵住这种可能的泄漏的最佳方法是什么?

If it can fail, what would be the best way to plug this possible leak?

创建另一个 SQL 查询以从数据库中获取正确的 ID 是否更安全，只是为了确定?

Would it be safer to create another SQL query to fetch the proper ID from the database, just to be sure?

推荐答案

只要 PDO 实现没有做一些真正愚蠢的事情，它总是安全的.以下来自last_insert_id上的MySQL信息:

It will always be safe provided that the PDO implementation is not doing something really bone-headed. The following is from the MySQL information on last_insert_id:

生成的 ID 以每个连接为基础在服务器中维护.这意味着函数返回给给定客户端的值是为该客户端影响 AUTO_INCREMENT 列的最新语句生成的第一个 AUTO_INCREMENT 值.此值不会受到其他客户端的影响，即使它们生成自己的 AUTO_INCREMENT 值.此行为可确保每个客户端都可以检索自己的 ID，而无需担心其他客户端的活动，也无需锁定或事务.

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.

相关文章