MySQL – LAST_INSERT_ID

Saving an order means inserting one row into customer_order and then several into order_item — and the line items need the order's id, which the database only chose a moment ago. LAST_INSERT_ID() is how you get it back.

The pattern

INSERT INTO customer_order
    (guest_email, customer_name, phone, order_type, status, subtotal, tax, delivery_fee, total,
     created_at, updated_at, public_id, deleted)
VALUES ('new@example.com', 'Robin Alvarez', '801-555-0199', 'CARRYOUT', 'PENDING_PAYMENT',
        0, 0, 0, 0, '2026-02-01', '2026-02-01', 'eeeeeeee-0000-4000-8000-000000000019', FALSE);

SET @order_id = LAST_INSERT_ID();

INSERT INTO order_item
    (order_id, product_id, product_name, size, quantity, unit_price, line_total,
     created_at, updated_at, public_id, deleted)
VALUES (@order_id,  2, 'Cheese Pizza', 'LARGE',  1, 15.49, 15.49,
        '2026-02-01', '2026-02-01', 'ffffffff-0000-4000-8000-000000000101', FALSE),
       (@order_id, 20, 'Pepsi',        'MEDIUM', 2,  2.49,  4.98,
        '2026-02-01', '2026-02-01', 'ffffffff-0000-4000-8000-000000000102', FALSE);

SELECT o.id AS order_id, o.customer_name, i.product_name, i.line_total
FROM   customer_order o JOIN order_item i ON i.order_id = o.id
WHERE  o.customer_name = 'Robin Alvarez'
ORDER  BY i.id;

+----------+---------------+--------------+------------+
| order_id | customer_name | product_name | line_total |
+----------+---------------+--------------+------------+
|       19 | Robin Alvarez | Cheese Pizza |      15.49 |
|       19 | Robin Alvarez | Pepsi        |       4.98 |
+----------+---------------+--------------+------------+

Note the SET @order_id = LAST_INSERT_ID(). Capturing it into a variable immediately is the safe habit, because the value changes with every subsequent insert — including the insert into order_item. Reading it again afterwards gives you something else entirely.

Why it is safe under concurrency

The obvious worry is that two people ordering at the same moment would get each other's ids. They do not: LAST_INSERT_ID() is per-connection. It reports the last value generated on your session, and nothing another connection does can change it. No locking, no race.

That is also why SELECT MAX(id) FROM customer_order is not a substitute. It reads whatever is committed globally, so under any concurrency it will eventually hand you somebody else's order — and the line items get attached to the wrong order. This is a genuine bug that appears only under load, which is the worst kind.

After a multi-row insert

The second insert above created two rows, which got ids 28 and 29. LAST_INSERT_ID() returns the first id of the batch, not the last. The rest are guaranteed consecutive from there — provided innodb_autoinc_lock_mode is not set to fully interleaved, which is a rare configuration.

So to get all of them, take the first and count forward. There is no way to ask MySQL for the whole list.

Three things that surprise people

• It does not see a trigger's inserts. If an AFTER INSERT trigger writes to an audit table with its own AUTO_INCREMENT, your LAST_INSERT_ID() still reports your row. Usually what you want, and worth knowing either way.
• It survives a rollback. The value is session state, not table state. Roll back the transaction and the row is gone while LAST_INSERT_ID() still returns its id — which is the same reason AUTO_INCREMENT leaves gaps.
• It returns 0 if the statement generated no id — an insert that supplied the id explicitly, or one that updated an existing row via ON DUPLICATE KEY UPDATE. Check for 0 rather than assuming.

From an application

You rarely call it directly. JDBC exposes the same value through generated keys:

try (PreparedStatement ps = conn.prepareStatement(
         "INSERT INTO customer_order (customer_name, order_type, status, ...) VALUES (?, ?, ?, ...)",
         Statement.RETURN_GENERATED_KEYS)) {
    ps.setString(1, "Robin Alvarez");
    ps.setString(2, "CARRYOUT");
    ps.setString(3, "PENDING_PAYMENT");
    ps.executeUpdate();

    long orderId;
    try (ResultSet keys = ps.getGeneratedKeys()) {
        if (!keys.next()) throw new IllegalStateException("no generated key");
        orderId = keys.getLong(1);
    }
    // ... insert the line items with orderId
}

RETURN_GENERATED_KEYS is required — without it getGeneratedKeys() returns an empty result set. JPA and Hibernate do the same thing for you: after persist(), the entity's id field is populated.

Wrap the whole thing in one transaction. An order whose line items failed to insert is worse than no order at all — see transactions.

The alternative: generate the id yourself

The pizza schema also gives every row a public_id CHAR(36) UUID, and the application generates that before inserting. That sidesteps the round trip entirely: you know the identifier without asking the database, so you can build the whole object graph and insert it in one go.

It is not a reason to drop the AUTO_INCREMENT key — a random UUID makes a poor clustered primary key, for the reasons in CREATE TABLE. Both, each doing the job it is good at.

What to remember

• Capture it into a variable immediately; the next insert overwrites it.
• It is per-connection, so it is safe under concurrency. MAX(id) is not.
• After a multi-row insert it returns the first id.
• It returns 0 when no id was generated.
• From Java, use RETURN_GENERATED_KEYS and one transaction.