Using LAST_INSERT_ID() in MySQL to Insert Related Data

Introduction When using AUTO_INCREMENT in MySQL, you often need to retrieve the ID of a newly inserted record. By using LAST_INSERT_ID(), you can easily obtain the ID generated from the most recent INSERT statement, making easier to insert related data. Specifically: Retrieve the ID of the parent table(orders) and link it to the child table(order_items). Automatically obtain the generated ID without manually setting it, ensuring data consistency. In such cases, LAST_INSERT_ID() is highly useful. This article explains how to write SQL queries utilizing this function and discusses its behavior when ROLLBACK is executed. Inserting Related Data Using LAST_INSERT_ID() Let's take the following two tables as an example: CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR(255) NOT NULL ); CREATE TABLE order_items ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, product_name VARCHAR(255) NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(id) ); SQL Example BEGIN; -- First order INSERT INTO orders (customer_name) VALUES ('Alice'); SET @order_id_1 = LAST_INSERT_ID(); INSERT INTO order_items (order_id, product_name) VALUES (@order_id_1, 'Product A1'), (@order_id_1, 'Product A2'); -- Second order INSERT INTO orders (customer_name) VALUES ('Bob'); SET @order_id_2 = LAST_INSERT_ID(); INSERT INTO order_items (order_id, product_name) VALUES (@order_id_2, 'Product B1'), (@order_id_2, 'Product B2'), (@order_id_2, 'Product B3'); COMMIT; Key Points Use LAST_INSERT_ID() immediately after INSERT to retrieve the newly generated id. Use the obtained id to insert related records into order_items. Start a transaction with BEGIN and finalize it with COMMIT. This method allows safe insertion of related data without manually checking IDs. What Happens When ROLLBACK is Used? If ROLLBACK is executed: BEGIN; INSERT INTO orders (customer_name) VALUES ('Charlie'); ROLLBACK; The AUTO_INCREMENT value is not reset, even though the insert operation is undone. Practical Example For instance, in an e-commerce system managing orders(orders) and order items (order_items): Even it an order is canceled, the id remains skipped Due to MySQL's AUTO_INCREMENT behavior, rolling back does not reset the id. Since this is the default MySQL behavior, it is essential to design your system to accommodate missing id values. Conclusion LAST_INSERT_ID() allows easy retrieval of the most recently inserted ID. It is the best way to correctly associate the ID of the parent table(orders) with the child table(order_items). Even after ROLLBACK, AUTO_INCREMENT values are not reset, so gaps in IDs should be considered in system design. Be leveraging this method, you can efficiently manage your database operations.

Feb 25, 2025 - 23:30

Using LAST_INSERT_ID() in MySQL to Insert Related Data

Introduction

When using AUTO_INCREMENT in MySQL, you often need to retrieve the ID of a newly inserted record. By using LAST_INSERT_ID(), you can easily obtain the ID generated from the most recent INSERT statement, making easier to insert related data.

Specifically:

Retrieve the ID of the parent table(orders) and link it to the child table(order_items).
Automatically obtain the generated ID without manually setting it, ensuring data consistency.

In such cases, LAST_INSERT_ID() is highly useful. This article explains how to write SQL queries utilizing this function and discusses its behavior when ROLLBACK is executed.

Inserting Related Data Using LAST_INSERT_ID()

Let's take the following two tables as an example:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL
);

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

SQL Example

BEGIN;

-- First order
INSERT INTO orders (customer_name) VALUES ('Alice');
SET @order_id_1 = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_name) VALUES (@order_id_1, 'Product A1'),
                                                         (@order_id_1, 'Product A2');

-- Second order
INSERT INTO orders (customer_name) VALUES ('Bob');
SET @order_id_2 = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_name) VALUES (@order_id_2, 'Product B1'),
                                                         (@order_id_2, 'Product B2'),
                                                         (@order_id_2, 'Product B3');

COMMIT;

Key Points

Use LAST_INSERT_ID() immediately after INSERT to retrieve the newly generated id.
Use the obtained id to insert related records into order_items.
Start a transaction with BEGIN and finalize it with COMMIT.

This method allows safe insertion of related data without manually checking IDs.

What Happens When ROLLBACK is Used?

If ROLLBACK is executed:

BEGIN;
INSERT INTO orders (customer_name) VALUES ('Charlie');
ROLLBACK;

The AUTO_INCREMENT value is not reset, even though the insert operation is undone.

Practical Example

For instance, in an e-commerce system managing orders(orders) and order items (order_items):

Even it an order is canceled, the id remains skipped
Due to MySQL's AUTO_INCREMENT behavior, rolling back does not reset the id.

Since this is the default MySQL behavior, it is essential to design your system to accommodate missing id values.

Conclusion

LAST_INSERT_ID() allows easy retrieval of the most recently inserted ID.
It is the best way to correctly associate the ID of the parent table(orders) with the child table(order_items).
Even after ROLLBACK, AUTO_INCREMENT values are not reset, so gaps in IDs should be considered in system design.

Be leveraging this method, you can efficiently manage your database operations.