lastInsertId () from PDO or SELECT MAX ('id') from MySQL

1

I am presented with the following question at the moment of wanting to insert in two tables, and this is what would be the best or most optimal at the moment of retrieving the last inserted id from a table so that later it will be inserted in another table that is properly related.

Always or almost always I have used the lastInsertId() of 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 a high user traffic this method could return an id that is not the correct one of the insertion 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 thought that the SELECT MAX(id) as last_id FROM usuarios; can be more useful because it returns the last inserted id of the table you want.

The question is, which is more useful to use and why, and which is more optimal.

    
asked by Alejo Mendoza 16.09.2018 в 02:59
source

2 answers

3

If it is to know the last id inserted in a self-incremental column at that precise moment it is clear that the most accurate option is:

SELECT MAX(id) as last_id FROM usuarios;

As is evident, the query will return the highest id that exists at that moment.

The PHP Manual information on lastInsertId is not clear, in fact, it does not claim something that is crucial to answer your question: this method returns the last id generated by the current connection .

Suppose then the following scenario:

  • Last current id: 44
  • Connection 1: inserts 3 records and then executes lastInsertId . For Connection 1 this value will be 47.
  • Connection 2: Insert 2 records and then execute lastInsertId . For Connection 2 this value will be 49.

We then have two different values for lastInsertId , which are 47 for Connection 1 and 49 for Connection 2.

What I am saying in this scenario is supported by what states the documentation on the behavior of LAST_INSERT_ID in MySQL :

  

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% co_of% value   generated for most recent statement affecting an AUTO_INCREMENT   column by that client . This value can not be affected by other   clients, even if they generate% co_of% 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.

  

The ID that was generated is kept on the server by connection .   This means that the value returned by the function to a given client   is the first value of AUTO_INCREMENT generated for the declaration   most recent that affects a column AUTO_INCREMENT for that   client . This value can not be affected by other clients,   even if they generate their own AUTO_INCREMENT values. East   behavior guarantees that each client can recover their own ID   without worrying about the activity of other clients, and without the need   of locks or transactions.

As the documentation says, AUTO_INCREMENT serves precisely to ensure that each connection can work with its own last generated id . If you want to know which is the last real id that exists in the table, the best way is to use AUTO_INCREMENT .

Other references

You can see the following questions and their answers:

answered by 16.09.2018 / 04:38
source
2

Responding to your question about user traffic;

You can use lastInsertId() of PDO without any problem in systems where you have many clients since each user connection is independent of the others, for example when you make a new insert and if MySQL successfully executes this query, then MySQL sends a OK_Packet as a response.

This means that PDO stores the information of the record inserted in that execution of the script.

On the server, select last_insert_id() is executed internally (Which is the one you recover with the%% PDO% method) is executed to fill this value in the OK package. The controller retrieves the last identification inserted from the package and, in turn, PDO retrieves the value from the controller.

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 lastInsertId() would be very dangerous

    
answered by 16.09.2018 в 04:21