MySQL和PDO：PDO :: lastInsertId理论上可以失败吗？ [英] MySQL and PDO: Could PDO::lastInsertId theoretically fail?

问题描述

我一直在思考这一段时间。

考虑一个巨大比例的Web应用程序，其中，让我们说，每秒钟执行数百万个SQL查询。

我运行我的代码：

  $ q = > prepare（'INSERT INTO Table 
（First，Second，Third，Fourth）
 VALUES（？，？，？，？） 
 $ q-> execute（array（$ first，$ second，$ third，$ fourth））; 
  

然后立即获取最后一个查询的自动递增ID：

  $ id = $ db-> lastInsertId（）; 
  

lastInsertId可能失败，即获取执行的一些SQL插入查询的ID <

>如果它可以失败，什么是最好的方式来堵塞这个可能的泄漏？

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

解决方案

这将始终是安全的，只要PDO实现不是做一些真正的头。以下是来自MySQL信息 last_insert_id ：

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

I have been pondering on this for a while.

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

I run my code:

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

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

$id = $db->lastInsertId();

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?

Secondary:

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

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

解决方案

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:

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.

这篇关于MySQL和PDO：PDO :: lastInsertId理论上可以失败吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！