How to get ID of the last updated row in MySQL?

In MySQL, obtaining the ID of the last inserted row is commonly used following insert operations, especially in tables utilizing auto-increment IDs. MySQL provides an internal function LAST_INSERT_ID() to retrieve the auto-increment ID of the most recently inserted row. This function is highly valuable because it is session-safe, meaning each user's connection independently records the ID of the last insert operation.

Example Usage Scenario

Suppose you have a table called orders that features an auto-increment primary key field order_id. When inserting a new order into this table, you may need to know the ID of the newly inserted order for subsequent operations, such as inserting order-related details into another table.

SQL Operation Examples

1. Insert Operation:

   sql
   INSERT INTO orders (product_id, quantity, customer_id) VALUES (15, 2, 101);

2. Retrieve the Last Inserted ID:

   sql
   SELECT LAST_INSERT_ID();

This SELECT LAST_INSERT_ID(); statement returns the order_id from the previously inserted row in the orders table.

Important Notes

• LAST_INSERT_ID() is only valid for the most recent INSERT operation and does not apply to UPDATE or DELETE operations.
• If no insert operation has been performed in the current session, LAST_INSERT_ID() returns 0.
• This function returns the ID of the last insert operation in the current session and is unaffected by insert operations in other sessions.

Summary

Using LAST_INSERT_ID() is a convenient and secure method to obtain the ID of the last inserted row, ensuring data consistency and operation independence. In multi-user environments, this is particularly crucial as it prevents ID conflicts between multiple users.