MySQL LAST_INSERT_ID() Function

 In MySQL, the LAST_INSERT_ID() function is used to retrieve the most recent auto-generated value that was inserted into an AUTO_INCREMENT column in the current session. This is often used to get the ID of the last inserted row when working with tables that have an auto-incrementing primary key.

Syntax:

LAST_INSERT_ID();

Description:

• LAST_INSERT_ID() returns the most recent automatically generated value that was inserted into an AUTO_INCREMENT column in the current session. This function is session-specific, meaning it will only return the ID from the current connection.
• If you insert a row into a table that has an AUTO_INCREMENT column, the LAST_INSERT_ID() function will return the last inserted value for that session.
• The value returned is a number corresponding to the ID assigned to the last inserted row.

Example:

1. Table Creation:

   CREATE TABLE users (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(100)
   );
   

2. Insert a Row:

   INSERT INTO users (name) VALUES ('Alice');
   

3. Retrieve Last Inserted ID:

   SELECT LAST_INSERT_ID();
   

   This will return the id of the row that was most recently inserted into the users table.

Important Notes:

• If the table does not have an AUTO_INCREMENT column, LAST_INSERT_ID() will return 0.
• If you perform multiple insert operations in a session, LAST_INSERT_ID() will always return the value generated by the most recent INSERT operation, even if you perform another INSERT into a different table.

Example Use Case:

When inserting a new row into a table with an AUTO_INCREMENT column, you might want to get the generated ID for use in another table or operation. Here's an example:

-- Insert a new user
INSERT INTO users (name) VALUES ('Bob');

-- Get the ID of the newly inserted user
SELECT LAST_INSERT_ID();