How does PDO's LastInsertId actually work?

Question

On the manual it's stated

Returns the ID of the last inserted row, or the last value from a sequence object, depending on the underlying driver.

Where if there's no $name variable passed as the first parameter to PDO::LastInsertID(), it'll check the last row that was inserted into the database. The state of this function also can be determined to work or not by the drivers that are stated, if you're using MySQL you're fine as that's heavily supported, but if you're using a database that doesn't support the OID or AUTO_INCRAMENT table definitions, PDO::LastInsertId()'s most likely not going to work.

That's all good and makes sense, but how is this achieved?

I understand PDO is just another abstraction layer to database connection, but we're also talking about the mysqli_insert_id function as well.

My idea of this method was that there's a cache (not on PHP's side), which I later discovered was true when reading this bit of code from warezthebeef at gmail dot com on the manual page.

// Assume $dbh connection handle is already established
$sql = "INSERT INTO product (product_name) OUTPUT INSERTED.product_id VALUES (?)";
$sth = $dbh->prepare($sql);
$sth->execute(array('widgets'));
$temp = $sth->fetch(PDO::FETCH_ASSOC);

echo "<pre>",print_r($temp),"</pre>";

This would display:

Array
(
    [product_id] => E1DA1CB0-676A-4CD9-A22C-90C9D4E81914
)

This is cause the table in the database would contain the row product_id and in the SQL you're stating to output that row after inserting, which would be the id for more common-ground terms, and if using AUTO_INCRAMENT would just be a single number. (you can read more into it by looking here) (this is also achievable with the RETURNING SQL keyword)

So, in a way if we didn't want to use the PDO::LastInsertID() method, we could just use:

$sql = "INSERT INTO table (name, country) OUTPUT INSERTED.id VALUES (?,?)";
$sth = $dbh->prepare($sql);
$sth->execute( ['jack', 'Australia'] );
$temp = $sth->fetch(PDO::FETCH_ASSOC);

echo $temp['id']

Which should output the new ID we just inserted into the database, noting that we use an AUTO_INCRAMENT primary or OID column.

So in my head (that's why I'm asking this question) PDO could be caching the data in PHP's Temp then retrieving or writing over it when the method is called, possibly adding the RETURNING id or such to it, but that's not the way it'd work since it's based on the OID column.

I answered half of the question - I'm just wondering how the method actually stores the last ID or pulls it

Thanks!

Answer 1

The exact implementation depends on the driver. I can only describe how it works for MySQL because that's what I'm familiar with.

As we described in the answers to How does MySqlCommand.LastInsertedId work? question:

If MySQL successfully executes a query requested by a client, then MySQL sends an OK_Packet as a response.

In the payload of the OK_Packet MySQL includes the last inserted id (see documentation linked above):

Type        | Name           | Description
------------|----------------|-----------------------------------
int<1>      | header         | [00] or [fe] the OK packet header 
int<lenenc> | affected_rows  | affected rows 
int<lenenc> | last_insert_id | last insert-id
...

On the server no select last_insert_id() is executed to populate this value into the OK_packet. The driver retrieves the last inserted id from the packet and PDO in turn retrieves the value from the driver.