How does MySqlCommand.LastInsertedId work?

Question

In a project of mine, I had to get the last inserted ID from the database so that I can sync newly inserted data with in memory data without doing a SELECT * FROM my_db; query i.e. I am doing a SELECT * FROM my_db WHERE id=new_index.

Now, I know that MySQL, which I'm using, supports the query select last_insert_id();, so I figured I would just make that command and execute it. But upon digging into C# (the programming language that I'm using for the project) I've discovered that the MySqlCommand has a LastInsertedId field.

Now what puzzles me is that I expected the getter of the field to just do the same select last_insert_id(); query, but upon looking at the logs from MySQL, I see no such query being made at all.

So my question is, how does MySqlCommand.LastInsertedId know what ID was assigned to the tuple that I'm inserting when the ID is auto generated by the MySQL server?

Answer 1

When the MySQL server responds to the client software (in your case it's Connector/Net) after an UPDATE query, it sends a so-called OK_Packet over the tcp (or socket) connection. That packet contains the last_insert_id value.

On the server it's available incidental to any INSERT query without another query, and so the wire-protocol handler just throws it in to the response packet.

The client software extracts that value from the packet and presents it to your application software on the LastInsertedId property.

Answer 2

If MySQL successfully executes a query requested by a client, then MySQL send 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
...

MySQL bug #65452 indicates that LastInsertId method reads the data out of a packet:

public int GetResult(ref int affectedRow, ref int insertedId)
{
...
insertedId = (int)packet.ReadFieldLength();
...
}

This indicates that the LastInsertId gets the value from the OK_Packet. On the server no select last_insert_id() is executed to populate this value into the OK_packet, this is why you do not see any such query in the query log.