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

1. Use LAST_INSERT_ID() immediately after INSERT to retrieve the newly generated id.
2. Use the obtained id to insert related records into order_items.
3. 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.