I am presented with the following question when I want to insert into two tables, and this is what would be the best or most optimal when retrieving the last id inserted from a table to later be inserted into another table that is properly related.
I have always or almost always used lastInsertId()
PDO, but more than all I have used it in projects where there is almost no user traffic. Reading the manual this method returns the last id inserted in the database in the current connection. Now I wonder, when the system has high user traffic, could this method return an id that is not the correct one from the insert that belongs to the script that was executed? since as the manual says the method returns the last id inserted in the database and not the last id inserted in a specific table.
For this I have thought that it SELECT MAX(id) as last_id FROM usuarios;
can be more useful since it returns the last inserted id of the desired table.
The question is, which one is more useful to use and why, and which one is more optimal.
If it is about knowing the last id inserted in an autoincremental column at that precise moment, it is clear that the most exact option is:
As is evident, the query will return the largest
id
that exists at that moment.The information in the PHP Manual about
lastInsertId
it is not clear, in fact, it does not state something that is decisive in answering your question: this method returns the last id generated by the current connection .Suppose then the following scenario:
44
lastInsertId
. For Connection 1 this value will be 47.lastInsertId
. For Connection 2 this value will be 49.We then have two different values for
lastInsertId
, which are47
for Connection 1 and49
for Connection 2.What I say in this scenario is supported by what the documentation about the behavior of
LAST_INSERT_ID
in MySQL says :As the documentation says, it
lastInsertId
serves precisely to ensure that each connection can work with its own last generated id . If what you want is to know what is the last real id that exists in the table, the best way is to useMAX(id)
.Other references
You can see the following questions and their answers:
Answering your question about user traffic;
you can use
lastInsertId()
PDO without any problem on systems where you have many clients since each user connection is independent of the others, for example when you make a new oneinsert
and if MySQL successfully executes this query, then MySQL sends aOK_Packet
as a response.This means that PDO stores the information of the record inserted in that execution of the script.
On the server, internally select
last_insert_id()
(which is the one you retrieve with thelastInsertId()
PDO method) is executed to fill this value in the OK packet. The handler retrieves the last inserted id from the package, and in turn, PDO retrieves the value from the handler.In summary it is safe to use
lastInsertId()
since the ID you retrieve will be the ID of that script execution and not that of another execution, unlike using itSELECT MAX(id)
would be very dangerous