AUTO_INCREMENT and LAST_INSERT_ID(): inserting related rows safely

Updated August 2026 · First published 2006 by David J. Lake

Part of the classic SQL Help series first published on this domain by David J. Lake (guelphdad). Rewritten from scratch and updated for MySQL 8 — same address, modern SQL.

The scenario: an article gets inserted into one table, and its category links (or tags, or details) go into a second table that references the article's id. The question is always the same — how do I get the new id to use in the second insert, without two users treading on each other?

The two wrong answers

Wrong answer #1: keep a counter yourself — in a file, a config row, or application memory. Two requests read the same counter at the same moment, both increment it, both insert with the same id. You've reinvented a race condition the database already solved.

Wrong answer #2: SELECT MAX(id)+1. Same race, different clothes. 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 — and if related rows were inserted in between, your tables now disagree with each other.

The right answer: AUTO_INCREMENT

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 your insert (or pass NULL) and MySQL assigns the next value atomically:

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

The id it just assigned is available as LAST_INSERT_ID() — and this is the crucial part — per connection. Another user inserting a millisecond later gets their own value; yours doesn't change until you insert another auto-increment row. No locks to manage, no coordination needed:

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

Several tables, several auto-increments

LAST_INSERT_ID() always reflects the most recent auto-increment insert on your connection — from any table. So if you insert into two auto-increment tables and then need the first id, save it in a session variable before it gets overwritten:

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 SET, the album insert would have silently replaced the artist's id and the track row would point at the wrong artist.

Wrap related inserts in a transaction. With InnoDB, START TRANSACTION ... COMMIT around the parent + child inserts means you never end up with an article that has no tags because the second statement failed. The auto-increment machinery works exactly the same inside a transaction.

From application code

Every driver exposes the same value without an extra query: PHP PDO's $pdo->lastInsertId(), mysqli's $mysqli->insert_id, Python mysql-connector's cursor.lastrowid, Node mysql2's result.insertId. They all read the per-connection value — same guarantees as calling LAST_INSERT_ID() yourself.

Two things people needlessly worry about

"I need to know the id before inserting." You don't — insert, then read it back. If you truly need pre-generated identifiers (distributed systems, offline clients), that's what UUIDs are for; ordinary web apps don't.

"Deleting rows leaves gaps in the sequence." It does, and that's fine. The id is an internal handle, not a row number — see gaps in AUTO_INCREMENT sequences for why you should leave them alone.