MySQL LAST_INSERT_ID Function

The LAST_INSERT_ID() function in MySQL returns the most recently generated AUTO_INCREMENT value for the last INSERT operation performed within the current session.

1. Syntax

SELECT LAST_INSERT_ID();

✅ Returns the last inserted AUTO_INCREMENT value for the session.

2. How LAST_INSERT_ID() Works

It remembers the last generated ID for the current connection.
It does not change if another session inserts a row.
Works only for tables with an AUTO_INCREMENT column.

3. LAST_INSERT_ID() Examples

Example 1: Getting the Last Inserted ID

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

INSERT INTO employees (name) VALUES ('Alice');
SELECT LAST_INSERT_ID() AS last_id;

🔹 Output:

last_id
1

✅ The last inserted ID is 1.

Example 2: Multiple Inserts

If you insert multiple rows, LAST_INSERT_ID() returns the first inserted ID.

INSERT INTO employees (name) VALUES ('Bob'), ('Charlie'), ('David');
SELECT LAST_INSERT_ID() AS last_id;

🔹 Output:

last_id
2

✅ It returns 2, which is Bob's ID, the first row inserted in this query.

Example 3: Using LAST_INSERT_ID() in Another INSERT

You can use LAST_INSERT_ID() to reference the generated ID:

INSERT INTO departments (name, manager_id)
VALUES ('HR', LAST_INSERT_ID());

✅ The last inserted employee's ID is used as the manager_id.

4. Difference Between LAST_INSERT_ID() and MAX(id)

Function	Description
`LAST_INSERT_ID()`	✅ Returns the last inserted ID for the session
`MAX(id)`	❌ Returns the highest ID in the table, even from other users

🚨 Warning: MAX(id) is not always reliable because rows may be deleted.

5. When to Use LAST_INSERT_ID()?

✅ After inserting a row with AUTO_INCREMENT, to get the new ID
✅ When inserting related records that depend on the last inserted row
✅ To maintain data consistency within a transaction

6. Conclusion

LAST_INSERT_ID() returns the last inserted AUTO_INCREMENT value in the session.
It is session-specific, meaning it won’t be affected by other users' inserts.
Use it to fetch IDs for related inserts and maintain referential integrity.

🚀 A must-know function when working with MySQL AUTO_INCREMENT fields!

Souy Soeng

MySQL LAST_INSERT_ID Function