Easy mysql question regarding primary keys and an insert

Question

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!

Answer 1

MySQL's LAST_INSERT_ID()

Answer 2

The MySQL Docs describe the function: LAST_INSERT_ID()

Answer 3

[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

Answer 4

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.