Mysql easy question regarding primary keys and insertion

Question

In mysql, how to get the primary key used for the insert operation when it is auto-incrementing.

Basically, I want the new auto-increment value to be returned when the instruction completes.

Thank!

Answer 1

The commentary on the explanations indicates that you are interested in LAST_INSERT_ID () not giving the wrong result if another simultaneous INSERT happens. Be sure that it is safe to use LAST_INSERT_ID () regardless of other simultaneous activity. LAST_INSERT_ID () returns only the most recent identifier generated during the current session.

You can try it yourself:

• Open two shell windows, start the mysql client in each and connect to the database.
• Shell 1: INSERT to table with AUTO_INCREMENT.
• Shell 1: SELECT LAST_INSERT_ID (), see the result.
• Shell 2: INSERT to the same table.
• Shell 2: SELECT LAST_INSERT_ID (), see result different from shell 1.
• Shell 1: SELECT LAST_INSERT_ID () again, see the repeat earlier result.

If you think about it, this is the only way that makes sense. All databases that support auto-increment key mechanisms should act this way. If the result depends on the race condition with other clients, possibly at the same time as INSERTing, then there will be no reliable way to get the last inserted ID value in the current session.

Answer 2

MySQL LAST_INSERT_ID ()

Answer 3

The MySQL Docs describe the function: LAST_INSERT_ID ()

Answer 4

[select max (primary_key_column_name) from table_name] Ahhh optional. I'm not a MySQL guy, but there is a certain way to get the last inserted id for the last completed action, which is a little more reliable than that. What should I do if the insert occurs between what you write in the table and query it? I know, because it stung me many moons ago (so yes, this is happening). If all else fails to read the manual: http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html