SQL Help

MySQL LAST_INSERT_ID(): insert related rows safely

Updated 8 November 2026

Based on the SQL Help series David J. Lake published here in 2006 to 2009. Rewritten for MySQL 8 by the WeFixTech editorial team.

Two monitors in an office showing code on dark screens, with a person in a light shirt seated out of focus and a pen cup in the foreground.

An article goes into one table. Its tags, or its category links, go into a second table that stores the article's id. The question is how to take the new id into that second insert without two connections using the same number.

LAST_INSERT_ID() returns the AUTO_INCREMENT value just generated on your own connection. Use that value. Do not predict the next id with SELECT MAX(id)+1.

The statements below follow the MySQL 8.4 information functions page and the Using AUTO_INCREMENT page, checked on 8 November 2026. They were not executed on a server for this page. Run them on a practice schema, not against tables that already hold data.

The two wrong answers

Wrong answer 1: keep a counter yourself, in a file, a config row, or application memory. Two requests can read the same counter at the same moment, both increment it, and both insert the same id.

Wrong answer 2: SELECT MAX(id)+1. Same race. Two connections both read MAX(id) as 100, both compute 101, and either you get duplicate ids or, with a primary key, one insert fails. If related rows were written in between, the tables no longer agree.

The right answer: AUTO_INCREMENT and LAST_INSERT_ID()

CREATE TABLE articles (
  article_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title      VARCHAR(200) NOT NULL,
  content    TEXT
);

CREATE TABLE article_tags (
  article_id INT UNSIGNED NOT NULL,
  tag        VARCHAR(50)  NOT NULL,
  PRIMARY KEY (article_id, tag)
);

Leave the id column out of the insert. MySQL generates the next value. Passing NULL does the same. Passing 0 also generates a value unless NO_AUTO_VALUE_ON_ZERO is enabled, so the examples here omit the column rather than send 0.

INSERT INTO articles (title, content)
VALUES ('Safe inserts', 'How auto_increment removes the race...');

The generated id is available as LAST_INSERT_ID(), and only on the connection that ran the insert. The MySQL 8.4 manual says this value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own, and that each client can retrieve its own id without locks or transactions. That protects the id. It does not make the article and its tags one change. Related rows still belong in one transaction. The transactions guide covers COMMIT and ROLLBACK in general. The rules for this function are below.

The function returns a BIGINT UNSIGNED. With no argument, it is the first automatically generated value successfully inserted for an AUTO_INCREMENT column by the most recently executed INSERT. If that statement inserts no row, the value is unchanged.

INSERT INTO article_tags (article_id, tag)
VALUES (LAST_INSERT_ID(), 'mysql'),
       (LAST_INSERT_ID(), 'tutorial');

article_tags has no AUTO_INCREMENT column, so this statement does not replace the stored id. The manual also says the statement that is running does not change LAST_INSERT_ID(), which is why both tag rows can call it and still receive the article id. If the child table has its own AUTO_INCREMENT column, save the parent id before that insert. After the child statement finishes, the stored value is the child's first generated id.

A multi-row INSERT returns the first id

If one INSERT generates several ids, LAST_INSERT_ID() returns the first of them, not the last. The manual's example inserts three rows, shows those rows as ids 2, 3 and 4, and shows LAST_INSERT_ID() returning 2. The Using AUTO_INCREMENT page says mysql_insert_id() does the same, so the statement can be reproduced on another server with the same first id.

Do not take that first id and assume the other new rows are the next integers. InnoDB's default innodb_autoinc_lock_mode is 2, interleaved. The InnoDB AUTO_INCREMENT page says values from one statement may not be consecutive when other inserts are allocating numbers at the same time. For a simple insert, with the row count known in advance and no other insert allocating values, that page says the numbers for that statement have no gaps. Keep the id LAST_INSERT_ID() returns, and read any further new ids from the rows if you need them.

Do not mix an explicit id and a generated id in the same INSERT if you are about to read the result. A specific non-zero id does not change LAST_INSERT_ID(). The C API mysql_insert_id() can still return the last explicit value. The SQL function and a driver helper can disagree. Omit the id column when you want the generated value.

Several tables, several auto-increments

LAST_INSERT_ID() is the most recent generated value on your connection, from whichever table produced it. Insert into two AUTO_INCREMENT tables and the second insert replaces the first id. Save each value before the next insert.

CREATE TABLE artists (
  artist_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(200) NOT NULL
);

CREATE TABLE albums (
  album_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title    VARCHAR(200) NOT NULL
);

CREATE TABLE tracks (
  track_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  artist_id  INT UNSIGNED NOT NULL,
  album_id   INT UNSIGNED NOT NULL,
  track_name VARCHAR(200) NOT NULL
);

INSERT INTO artists (name) VALUES ('Black Sabbath');
SET @artist_id = LAST_INSERT_ID();

INSERT INTO albums (title) VALUES ('Paranoid');
SET @album_id = LAST_INSERT_ID();

INSERT INTO tracks (artist_id, album_id, track_name)
VALUES (@artist_id, @album_id, 'War Pigs');

Without the two SET statements, the album insert would replace the artist id, and the track row would point at the wrong artist. The track insert omits track_id, so it generates one and, once the statement has finished, replaces LAST_INSERT_ID() with that track id. The artist and album ids in the row come from the variables. If you also need the track id, save LAST_INSERT_ID() after the track insert.

A trigger that inserts into another AUTO_INCREMENT table changes the value while the trigger runs, then restores it when the trigger ends. A stored function does the same. A stored procedure does not: statements after the CALL see the procedure's value. mysql_insert_id() after a CALL returns 0, because it applies to the CALL rather than to the statement inside. Use LAST_INSERT_ID() in SQL, or return the id from the procedure. See stored procedures.

Transactions, errors, and ROLLBACK

Put the parent and child inserts in one transaction so you can undo the parent if a later insert fails. Check each statement. On failure, issue ROLLBACK and discard the id. Do not COMMIT a partial set.

START TRANSACTION;
INSERT INTO articles (title, content)
VALUES ('Safe inserts', 'How auto_increment removes the race...');
SET @article_id = LAST_INSERT_ID();
INSERT INTO article_tags (article_id, tag)
VALUES (@article_id, 'mysql'),
       (@article_id, 'tutorial');
COMMIT;

The block above is the pattern. It is not a promise that any failure rolls the whole transaction back for you. The InnoDB error handling page says a duplicate-key error rolls back the statement, a lock-wait timeout rolls back the waiting statement unless the server was started with innodb_rollback_on_timeout, and a deadlock rolls back the entire transaction. A failed statement does not, by itself, remove an earlier successful insert from the same transaction. If you commit after ignoring that failure, the article is stored without the tags.

What happens to the id. The manual separates two cases. If the previous statement returned an error, LAST_INSERT_ID() is undefined. For a transactional table, a statement rolled back because of an error leaves the value undefined. A manual ROLLBACK does not restore the value from before the transaction: it stays as it was at the ROLLBACK. Do not read the function after an error, and do not treat the value after ROLLBACK as a row that still exists. InnoDB does not put the generated number back. That lost value is one reason ids have gaps, covered under why ids skip.

Save the id before the next insert: checklist

  1. Run the parent INSERT and confirm it succeeded. If it returned an error, do not read LAST_INSERT_ID().
  2. Save the id immediately: SET @id = LAST_INSERT_ID(); in SQL, or the driver helper on that same connection. Do this before COMMIT and before any other statement on the connection.
  3. If another AUTO_INCREMENT insert will run on this connection, use the saved id. A trigger restores the previous value when it ends. A stored procedure does not.
  4. When one statement inserts several parent rows, the returned id is the first generated value. Do not count upward from it.
  5. On a connection pool, read the id from the connection or from the result object that performed the insert, before that connection goes back to the pool.
  6. Put the parent and child inserts in one transaction. If a later statement fails, ROLLBACK and discard the saved id.

LAST_INSERT_ID() compared with the other ways to get an id

Method Safe with concurrent inserts? Extra round trip? When to use
LAST_INSERT_ID() Yes. The value is per connection. Other clients cannot change it. No, if the next INSERT calls it. A separate SELECT is optional. SQL scripts and stored procedures, on the connection that inserted.
Driver helpers: PDO lastInsertId(), mysqli insert_id, Connector/Python lastrowid, mysql2 insertId Yes. They read that same per-connection value. Read them immediately, on the connection that inserted. No. The id comes back with the insert result. Application code.
LAST_INSERT_ID(expr) on a sequence table Yes. The manual calls this multi-user safe: each client gets its own new value. One UPDATE, then read the value. Your own counter, not a normal table's AUTO_INCREMENT.
SELECT MAX(id)+1 No. Two connections can read the same maximum. Yes. Not for new ids.
Homemade counter, in a file, a config row, or memory No. Two requests can read the same value. Depends on where the counter lives. Not for new ids.

Sources for the table and the chart: the MySQL 8.4 pages linked above, plus the PHP, Connector/Python, mysql2 and Connector/ODBC pages linked in the next section. Checked 8 November 2026.

Bar chart of editorial concurrency-safety scores for getting a new row id in MySQL: LAST_INSERT_ID() on the same connection 2 out of 2, driver helpers 2 out of 2, LAST_INSERT_ID(expr) sequence table 2 out of 2, SELECT MAX(id)+1 0 out of 2, homemade counter 0 out of 2. Score 2 means a per-connection value other clients cannot change. Score 0 means two connections can read the same value. Based on the MySQL 8.4 manual, checked 8 November 2026. Not a server benchmark.
Editorial concurrency-safety scores: LAST_INSERT_ID() on the same connection 2 out of 2, driver helpers such as lastInsertId and insert_id 2 out of 2, LAST_INSERT_ID(expr) sequence table 2 out of 2, SELECT MAX(id)+1 0 out of 2, homemade counter 0 out of 2. Score 2 means a per-connection value other clients cannot change. Score 0 means two connections can read the same value and collide. Sources: MySQL 8.4 information functions and Using AUTO_INCREMENT, plus the PHP, Connector/Python and mysql2 docs. Checked 8 November 2026.

The same scores in text, so the chart does not have to be enlarged: LAST_INSERT_ID() on the same connection, 2 out of 2. Driver helpers, 2 out of 2. LAST_INSERT_ID(expr) on a sequence table, 2 out of 2. SELECT MAX(id)+1, 0 out of 2. A homemade counter, 0 out of 2. A 2 is not a speed score, and it does not mean a driver helper can be read after another statement. The bars were not measured by running these methods on a server.

From application code

Read the helper immediately, on the connection that ran the insert. The C API says mysql_insert_id() returns 0 if the previous statement does not use an AUTO_INCREMENT value, and that its value is undefined if that statement returned an error. COMMIT is a later statement. Read the helper before you commit, and before you run anything else on that connection. The SQL function is different: it stays until a later statement generates a new value, an error leaves it undefined, or you roll back, as described above.

  • PHP PDO: $pdo->lastInsertId(), with no sequence name. The PHP manual says results can differ between PDO drivers, and that the method returns a string. This note is for the MySQL driver. Call it on the same PDO instance that ran the insert.
  • PHP mysqli: $mysqli->insert_id, or $stmt->insert_id for a prepared statement. The PHP manual says only statements on the current connection affect it, that a multi-row insert returns the first generated value, and that the property must be read immediately. A value bigger than the platform integer comes back as a string.
  • Python, MySQL Connector/Python: cursor.lastrowid. The Connector/Python guide says this property is like mysql_insert_id(), which returns the first generated value when one statement inserts several rows. It is None when no such value is available.
  • Node.js mysql2: insertId on the ResultSetHeader returned with the insert. Hold that result. Do not expect a later query on a pooled connection to still be the insert.

For ODBC, the Connector/ODBC page uses a second statement, SELECT LAST_INSERT_ID(), or puts LAST_INSERT_ID() in the next INSERT. That page also shows SELECT * FROM tbl WHERE auto_col IS NULL for some desktop apps. The sql_auto_is_null page says the comparison works only when the variable is on, and that the default is off. Leave it off, and use LAST_INSERT_ID().

LAST_INSERT_ID(expr) and ON DUPLICATE KEY UPDATE

LAST_INSERT_ID(expr) returns expr and stores it as the value the next LAST_INSERT_ID() call returns. The manual's sequence example is a one-row table:

CREATE TABLE sequence (id INT NOT NULL);
INSERT INTO sequence VALUES (0);
UPDATE sequence SET id = LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID();

The manual describes that UPDATE as safe for multiple users: each client reads its own new value, and other clients' values do not change it. mysql_insert_id() can read it too, but only after an INSERT or UPDATE. A following SELECT or SET does not update the C API value. Do not put LAST_INSERT_ID() and LAST_INSERT_ID(expr) in the same statement. The manual says that effect is undefined. This pattern is a counter of your own. It is not a substitute for AUTO_INCREMENT on an ordinary table.

For INSERT ... ON DUPLICATE KEY UPDATE, the 8.4 manual says that if the table has an AUTO_INCREMENT column and the statement inserts or updates a row, LAST_INSERT_ID() returns the AUTO_INCREMENT value. The same page says an InnoDB insert still advances the counter when the duplicate path is an update. That sentence does not promise the returned value is the id of the row you updated. To store the existing row's id, assign the column with LAST_INSERT_ID(expr) in the update list, for example article_id = LAST_INSERT_ID(article_id). Use a row alias for the other new values. VALUES(col) in that clause is deprecated in 8.4.

Affected rows, per row, from that same page: 1 if the row was inserted, 2 if an existing row was updated, and 0 if an existing row was set to its current values. If the client connected with CLIENT_FOUND_ROWS, an unchanged row counts as 1, not 0. Check the flag before you treat 0 as "nothing changed".

Two things people needlessly worry about

"I need to know the id before inserting." Usually you do not. Insert the row, then read the id. If you truly need the id beforehand, for an offline client or a distributed system, generate a UUID in the application and store that. A UUID is not an AUTO_INCREMENT value, and LAST_INSERT_ID() will not invent it for you.

Why are there gaps in my IDs?

"My ids go 1, 2, 3, 7, 12. How do I fix the gaps?"

Short answer: you don't. There is nothing to fix.

Where gaps come from

Gaps are a normal by-product of how InnoDB hands out auto-increment values. The InnoDB page says that once a value is generated it cannot be rolled back, whether or not the insert completed and whether or not the transaction rolled back. Lost values are not reused. You will see gaps from:

  • Deleted rows. Delete row 5 on an InnoDB table and there will not be another 5. MyISAM can reuse the highest value in a group when AUTO_INCREMENT is a secondary column in a multi-column index. InnoDB is the default engine in MySQL 8.
  • Rolled-back transactions. A value once claimed is not returned, even if the insert never commits.
  • INSERT IGNORE and failed inserts. If INSERT IGNORE ignores the row, LAST_INSERT_ID() stays unchanged. For InnoDB, the counter still moves forward when innodb_autoinc_lock_mode is 1 or 2. The default in 8.4 is 2. The manual's printed demonstration uses mode 1. A statement that returns an error leaves LAST_INSERT_ID() undefined, which is a different case from an ignored row.
  • ON DUPLICATE KEY UPDATE. On InnoDB, the insert can advance the counter even when the statement updates the existing row.
  • Bulk inserts. In lock modes 1 and 2, a bulk insert can reserve more values than it uses. In the default interleaved mode, numbers from one statement can also have gaps when other inserts run at the same time.

None of these are a broken sequence. The engine's promise is that each stored value is unique, not that the numbers are dense. The current maximum is written to the redo log and saved in the data dictionary, so a normal restart does not start again at 1. After an unexpected exit, the same page says a previously allocated value can be reused if the new maximum had not yet been flushed to disk.

If you use statement-based replication, set innodb_autoinc_lock_mode to 0 or 1 and use the same value on the source and the replicas. The default of 2 is the row-based setting. Auto-increment values are not guaranteed to match when the lock mode is 2.

Why renumbering is the wrong fix

A surrogate key's one job is to identify a row, forever. The moment you renumber rows to close gaps:

  • every foreign key referencing those rows must be rewritten in lockstep, or the data is silently corrupted;
  • every external reference, such as a bookmarked URL, an invoice, a log line, or an email that mentions an order number, now points at the wrong row;
  • and the very next delete puts a gap right back.

If something in an application breaks because ids are not consecutive, the application is misusing the id. Users should never be shown a row count via ids. That is what COUNT(*) is for. "Sequence number 5 of 12" is a display-time calculation, ROW_NUMBER() in MySQL 8, not a storage concern. This page does not include a renumbering statement. Closing the gaps would be the bug.

What ids are for, and what they are not for

Use an auto-increment surrogate key when the table has no natural, stable, unique column to serve as the primary key, which in practice is most tables. Treat the value as an opaque handle: fine to use in joins, foreign keys and URLs, meaningless as data. If you need a gapless, human-facing sequence, invoice numbers are the textbook case. Generate that as its own column with explicit locking, and let the primary key be a separate internal id. The sequence-table UPDATE earlier on this page is one documented way to build that counter. It is a different job from reading LAST_INSERT_ID() after a normal insert.

FAQ

How does LAST_INSERT_ID work in MySQL?

When an INSERT successfully generates an AUTO_INCREMENT value, LAST_INSERT_ID() returns the first value that statement generated. MySQL keeps that value for your connection. Other clients cannot change it. If the statement inserts no row, the value stays as it was.

Is LAST_INSERT_ID safe with concurrent inserts?

Yes, if you read it on the same connection that ran the insert. The MySQL 8.4 manual says each client can retrieve its own id without locks or transactions. That protects the id value only. Put the related inserts in one transaction, and ROLLBACK if a later statement fails.

Does a multi-row INSERT return the first or last id?

The first. One INSERT that adds several rows makes LAST_INSERT_ID() return the first generated id, not the last. Do not add 1 and 2 to that id and assume those are the other rows. InnoDB's default lock mode can interleave values with other inserts.

How do I get LAST_INSERT_ID from PHP, Python or Node?

Read the helper on the same connection, immediately after the insert: PDO lastInsertId(), mysqli insert_id, Connector/Python lastrowid, or mysql2 insertId. The C API returns 0 if the previous statement does not use an AUTO_INCREMENT value, so read the helper before COMMIT or any later statement.

Does ROLLBACK reset LAST_INSERT_ID?

A manual ROLLBACK does not restore the earlier value. It stays as it was at the ROLLBACK. If a statement returns an error, the MySQL 8.4 manual says the value is undefined, so do not read it. InnoDB does not put a rolled-back auto-increment value back into the counter.

Where can I learn SQL basics properly?

The best SQL books for beginners guide lists MySQL-friendly first books. For a text date column, the VARCHAR to DATE guide covers STR_TO_DATE.