How Can I Retrieve a MySQL Auto-Incrementing Primary Key After an INSERT Query?

How to Retrieve the Newly Generated Primary Key from a MySQL INSERT Query

When executing an INSERT query in MySQL into a table with an auto-incrementing primary key column, the need arises to obtain the value of the newly generated primary key without having to resort to a second query. This practice ensures correctness and eliminates the risk of obtaining an incorrect ID.

Using the LAST_INSERT_ID() Function

MySQL provides the LAST_INSERT_ID() function to retrieve the primary key value of the last inserted row by the current client connection. By incorporating this function into the query, you can capture the new ID immediately after inserting a record.

For instance, consider an INSERT query:

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

To retrieve the newly generated primary key, add the following line:

SELECT LAST_INSERT_ID();

This will return the ID of the row just inserted by the client.

Per-Connection Scope

It's important to note that LAST_INSERT_ID() maintains the generated ID on a per-connection basis. This means that concurrent queries from other clients will not affect its value. Each client will only receive the ID generated for its own queries.

By utilizing LAST_INSERT_ID() effectively, you can ensure that you always retrieve the correct primary key value from a MySQL INSERT query, streamlining your database operations and eliminating any potential issues with incorrect IDs.

The above is the detailed content of How Can I Retrieve a MySQL Auto-Incrementing Primary Key After an INSERT Query?. For more information, please follow other related articles on the PHP Chinese website!