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: