mysqlinsertkeyauto-increment

Get the new record primary key ID from MySQL insert query?

Let's say I am doing a MySQL INSERT into one of my tables and the table has the column item_id which is set to autoincrement and primary key.

How do I get the query to output the value of the newly generated primary key item_id in the same query?

Currently I am running a second query to retrieve the id but this hardly seems like good practice considering this might produce the wrong result...

If this is not possible then what is the best practice to ensure I retrieve the correct id?

Solution

  • You need to use the LAST_INSERT_ID() function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

    Eg:

    INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();

    This will get you back the PRIMARY KEY value of the last row that you inserted:

    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 AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.

    So the value returned by LAST_INSERT_ID() is per user and is unaffected by other queries that might be running on the server from other users.