8

In mysql, how do I get the primary key used for an insert operation, when it is autoincrementing.

Basically, i want the new autoincremented value to be returned when the statement completes.

Thanks!

Grace Note
  • 3,205
  • 4
  • 35
  • 55
MPX
  • 1,075
  • 3
  • 10
  • 9
  • Clarification: Code has insert then a last_insert_id. Two of these statements execute almost at the same time and the exec order turns into insert #1 insert #2 last_insert_id #1 completes and returns the id from insert#2 last_insert_id #2 completes and returns the id from insert#2 Can this happen? – MPX Oct 03 '08 at 00:30

4 Answers4

12

Your clarification comment says that you're interested in making sure that LAST_INSERT_ID() doesn't give the wrong result if another concurrent INSERT happens. Rest assured that it is safe to use LAST_INSERT_ID() regardless of other concurrent activity. LAST_INSERT_ID() returns only the most recent ID generated during the current session.

You can try it yourself:

  1. Open two shell windows, run mysql client in each and connect to database.
  2. Shell 1: INSERT into a table with an AUTO_INCREMENT key.
  3. Shell 1: SELECT LAST_INSERT_ID(), see result.
  4. Shell 2: INSERT into the same table.
  5. Shell 2: SELECT LAST_INSERT_ID(), see result different from shell 1.
  6. Shell 1: SELECT LAST_INSERT_ID() again, see a repeat of earlier result.

If you think about it, this is the only way that makes sense. All databases that support auto-incrementing key mechanisms must act this way. If the result depends on a race condition with other clients possibly INSERTing concurrently, then there would be no dependable way to get the last inserted ID value in your current session.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • @Bill Karwin thx a lot for this helpful post. A question: Say in a multi threaded environment the last Auto inc key value available was 10.. then the thread of shell 1 fails to insert and at the same moment shell two is inserting.. what would be the last_insert_id() ? 11 or 12? thx a lot – ccot Feb 01 '12 at 18:03
  • @shadesco: Depends on your version of MySQL, but in 5.1 and later with InnoDB, the first session would increment the auto-inc and that value would be lost. Then the second session would get 12. See http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html – Bill Karwin Feb 02 '12 at 03:56
6

MySQL's LAST_INSERT_ID()

Jonathan Lonowski
  • 121,453
  • 34
  • 200
  • 199
0

The MySQL Docs describe the function: LAST_INSERT_ID()

Nick
  • 296
  • 1
  • 4
  • 5
-1

[select max(primary_key_column_name) from table_name] Ahhh not nessecarily. I am not an MySQL guy but there are specific way to get the last inserted id for the last completed action that are a little more robust than this. What if an insert has happened between you writing to the table and querying it? I know about because it stung me many moons ago (so yeah it does happen). If all else fails read the manual: http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

RhysC
  • 1,644
  • 1
  • 15
  • 23