1

I didn't find an exact answer so I try to ask here. I running an SQL INSERT Query and after that I want to use the ID of the this INSERT Query, I found this: LAST_INSERT_ID() and it's work, but I thinking what would happen if I not the only one who run the query on the server I can get a wrong ID from the DB, so there is other to take the last ID ?

user3629794
  • 27
  • 6
  • If you are using LAST_INSERT_ID() in the insert query, you should not worry about that because the both operations (getting the last ID and insertion) will happen in the context of a single transaction. – izilotti Feb 21 '15 at 19:06
  • Take a look at this other post that deals with the same scenario. http://stackoverflow.com/questions/3837990/last-insert-id-mysql – WorkSmarter Feb 21 '15 at 19:21

3 Answers3

2

This is both an answer and a clarification of the misconceptions presented elsewhere in this Q&A.

After you do an INSERT into a table with an AUTO_INCREMENT, the value of that AI is waiting for you in that connection; you can fetch it via LAST_INSERT_ID (or whatever synonym your client's API has).

It is tied to the connection, not a transaction.

Being tied to the connection, no other connection can accidentally get the id that you just created.

MAX(id) does run the risk of another connection INSERTing another row, thereby bumping id beyond the value you just inserted. Hence, MAX(id) could give you the wrong value.

If you need to do INSERT..ON DUPLICATE UPDATE, the online manual has a clear example of how to get the id of the row, whether it was INSERTed or UPDATEd. It requires that you the kludgy looking id = LAST_INSERT_ID(id) in the UPDATE part.

The only requirement for an AUTO_INCREMENT be that it is the first column in some index, not necessarily the PRIMARY KEY. (MyISAM has an exception, for which InnoDB has no counterpart.) What you could lose by not saying PRIMARY KEY(id) is the prevention of duplicate ids when you explicitly INSERT the same id again.

Rick James
  • 135,179
  • 13
  • 127
  • 222
1

It should return the last inserted row id from the same connection. So if other user has inserted another row in between, LAST_INSERT_ID function will still return the relevant idfor the first user.

Quoting from mysql documentation:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function(LAST_INSERT_ID) to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client

Here is the link http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id .

Vivek Vaghela
  • 1,075
  • 9
  • 16
0

you can get last id from your table like this it would be better if id is auto increment

Select max(id) from yourtable
Khurram Ali
  • 1,659
  • 4
  • 20
  • 37
  • 1
    This could be problematic if multiple people are using the system at the same time. There is no guarantee the max(id) will return the desired id. – WorkSmarter Feb 21 '15 at 19:19