AUTO_INCREMENT — MySQL

AUTO_INCREMENT in MySQL

AUTO_INCREMENT is MySQL's built-in mechanism for automatically generating unique integer values for a column. It is the most common way to create a surrogate primary key — you insert a row without specifying the ID and MySQL assigns the next available value.

Basic Usage

SQL

CREATE TABLE users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

-- Insert without specifying id
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
INSERT INTO users (username, email) VALUES ('bob',   'bob@example.com');

SELECT id, username FROM users;
-- id=1 alice
-- id=2 bob

Note

The AUTO_INCREMENT column must be part of a key (usually the PRIMARY KEY). It must also be an integer type: TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT (signed or unsigned).

How InnoDB Manages the Counter

InnoDB stores the current AUTO_INCREMENT counter in memory, not in the table data itself. On startup, InnoDB initialises the counter by reading MAX(id) + 1 from the table. This has an important consequence: if you restart the server after deleting high-valued rows, the counter resets to just above the new maximum — so IDs previously rolled back and "lost" may reappear after a restart in older MySQL versions.

MySQL 8.0 fixed this by persisting the counter to the redo log, so it survives restarts without scanning the table.

SQL

-- Check current AUTO_INCREMENT value for a table
SHOW TABLE STATUS LIKE 'users'G
-- Auto_increment: 3 (next value to be assigned)

-- Also available in information_schema
SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users';

Setting the Starting Value

You can control where the counter starts — useful when migrating data, leaving room for manually inserted IDs, or reserving a range.

SQL

-- Set starting value at table creation
CREATE TABLE invoices (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  amount DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1000;

-- Change the counter on an existing table
ALTER TABLE invoices AUTO_INCREMENT = 5000;

-- Verify
SHOW TABLE STATUS LIKE 'invoices'G
-- Auto_increment: 5000

Tip

You can only increase the AUTO_INCREMENT counter; you cannot set it lower than the current maximum value in the column. MySQL silently ignores attempts to decrease it below the max.

Resetting After Deletes — Why Gaps Appear

After deleting rows, the AUTO_INCREMENT counter does NOT decrease. This is intentional. Gaps in IDs are normal and expected — they occur from:

Rolled-back transactions (the allocated ID is permanently consumed)
Rows deleted after insertion
INSERT ... ON DUPLICATE KEY UPDATE when the insert path triggers the counter
innodb_autoinc_lock_mode = 2 pre-allocating IDs in bulk for performance

Gaps are harmless. Never write application logic that assumes IDs are sequential.

SQL

-- After deleting rows, the counter does NOT automatically reset
DELETE FROM users WHERE id = 3;
INSERT INTO users (username, email) VALUES ('carol', 'carol@example.com');
-- carol gets id=4, not id=3 — gaps are normal

-- To force a reset to max(id)+1:
-- Option 1: TRUNCATE (resets to 1, but removes all data)
TRUNCATE TABLE users;

-- Option 2: Manually reset to just above the max existing id
SELECT MAX(id) FROM users;  -- suppose result is 10
ALTER TABLE users AUTO_INCREMENT = 11;

Warning

Never rely on AUTO_INCREMENT values being gap-free or contiguous. Gaps are normal due to rollbacks, deletes, and failed inserts. If you need sequential numbers, implement a separate sequence table.

Getting the Last Inserted ID

After an INSERT, MySQL stores the generated ID in a session-local variable. Use LAST_INSERT_ID() to retrieve it immediately.

Key properties of LAST_INSERT_ID():

Session-scoped: other connections' inserts do not affect your value
Statement-scoped: it is set by the most recent INSERT in your session
Safe for concurrency: two sessions inserting simultaneously each see their own ID

SQL

INSERT INTO users (username, email)
  VALUES ('dave', 'dave@example.com');

SELECT LAST_INSERT_ID();
-- Returns the auto-generated id for dave

-- Use it in the same transaction to insert related rows
INSERT INTO users (username, email)
  VALUES ('eve', 'eve@example.com');
SET @new_user_id = LAST_INSERT_ID();

INSERT INTO user_profiles (user_id, bio)
  VALUES (@new_user_id, 'Hello world');

Note

LAST_INSERT_ID() is connection-scoped. Concurrent inserts from other connections do not affect what you see — you always get the ID generated by your own session.

Multi-Row INSERT and LAST_INSERT_ID

When inserting multiple rows in a single statement, LAST_INSERT_ID() returns the ID of the first inserted row — not the last. You can then calculate subsequent IDs by adding the number of rows inserted.

SQL

-- Insert multiple rows at once
INSERT INTO users (username, email) VALUES
  ('frank', 'frank@example.com'),
  ('grace', 'grace@example.com'),
  ('hank',  'hank@example.com');

-- LAST_INSERT_ID() returns the id of the FIRST inserted row
SELECT LAST_INSERT_ID();
-- e.g. 5 (frank's id), even though hank got id=7

-- Calculate all IDs: LAST_INSERT_ID() + 0, +1, +2
-- frank=5, grace=6, hank=7

Tip

When doing bulk inserts you can calculate subsequent IDs: if LAST_INSERT_ID() returns 5 and you inserted 3 rows, the IDs are 5, 6, and 7.

Gap Behaviour in InnoDB

InnoDB pre-allocates AUTO_INCREMENT values in memory for performance. If a transaction is rolled back, those IDs are lost — creating gaps. This is by design and not a bug.

SQL

START TRANSACTION;
INSERT INTO users (username, email)
  VALUES ('ivan', 'ivan@example.com');  -- gets id=10
ROLLBACK;  -- id=10 is permanently lost

INSERT INTO users (username, email)
  VALUES ('judy', 'judy@example.com');  -- gets id=11, not id=10

innodb_autoinc_lock_mode

The innodb_autoinc_lock_mode setting controls how InnoDB acquires AUTO_INCREMENT values. It is a trade-off between performance and the strictness of ID allocation.

Mode	Name	Behaviour	Replication
0	traditional	Table-level lock held for entire INSERT — slowest, fully predictable IDs	Safe with statement-based
1	consecutive	Lock for multi-row inserts, not for simple inserts — default before 8.0	Safe with statement-based
2	interleaved	No table lock at all — fastest, IDs may have gaps within bulk inserts	Requires row-based (default 8.0+)

SQL

-- Check current setting
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

-- Set in my.cnf / my.ini (requires restart)
-- [mysqld]
-- innodb_autoinc_lock_mode = 2

AUTO_INCREMENT with Composite Primary Keys

InnoDB requires AUTO_INCREMENT to be the first column of the index. With composite PKs you often need a workaround. MyISAM is more flexible here but should not be used for new tables.

SQL

-- This works in MyISAM (not InnoDB):
CREATE TABLE hits (
  user_id INT UNSIGNED NOT NULL,
  hit_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  hit_at  DATETIME NOT NULL,
  PRIMARY KEY (user_id, hit_id)  -- auto_increment on second column
) ENGINE=MyISAM;

-- InnoDB equivalent: use a surrogate PK + unique constraint
CREATE TABLE hits (
  id      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  hit_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  INDEX idx_user (user_id)
) ENGINE=InnoDB;

UUID as an Alternative

For distributed systems where multiple servers generate IDs independently, UUIDs avoid collisions without a central counter. MySQL 8.0 introduced ordered UUIDs for better index performance.

Random UUID primary keys cause heavy B-tree page splits because new rows insert at random positions throughout the index. Ordered UUIDs or ULID-style values mitigate this by clustering inserts near the end of the index like AUTO_INCREMENT does.

SQL

-- Classic UUID (random, poor index locality)
CREATE TABLE events_uuid (
  id   CHAR(36) NOT NULL DEFAULT (UUID()),
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

-- Ordered UUID using UUID_TO_BIN with swap_flag=1 (MySQL 8.0+)
-- Rearranges UUID bytes so time-based portion comes first -> better B-tree locality
CREATE TABLE events_ordered (
  id   BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)),
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

-- Read back
SELECT BIN_TO_UUID(id, 1) AS uuid, name FROM events_ordered;

Practical Bulk Insert Pattern with LAST_INSERT_ID

A common pattern: insert a parent record, capture its ID, then insert all child records referencing that ID — all within a single transaction for atomicity.

SQL

START TRANSACTION;

-- Insert the parent order
INSERT INTO orders (customer_id, total, created_at)
VALUES (42, 149.97, NOW());

SET @order_id = LAST_INSERT_ID();

-- Insert all line items referencing the new order ID
INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES
  (@order_id, 101, 2, 29.99),
  (@order_id, 205, 1, 89.99);

COMMIT;

-- Verify
SELECT o.id, o.total, COUNT(i.id) AS item_count
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.id = @order_id
GROUP BY o.id, o.total;

Comparison: ID Strategies

Strategy	Storage	Pros	Cons
INT AUTO_INCREMENT	4 bytes	Simple, compact, fast reads	Single server, gaps possible, ~4B limit
BIGINT AUTO_INCREMENT	8 bytes	Handles huge row counts	Same as INT, larger range
UUID (random CHAR(36))	36 bytes	Globally unique, distributed	Large, poor index locality
UUID_TO_BIN ordered (8.0+)	16 bytes	Globally unique, better locality	Complex, still 16 bytes
Application-generated ULID	16 bytes	Sortable, globally unique	Must guarantee uniqueness yourself

Best Practices

Use INT UNSIGNED (max ~4.3 billion) or BIGINT UNSIGNED for very large tables.
Never expose AUTO_INCREMENT IDs in public URLs for security-sensitive resources — use a separate public token or UUID.
Do not depend on sequential IDs without gaps — rollbacks and concurrent inserts create gaps intentionally.
Always use LAST_INSERT_ID() instead of SELECT MAX(id) to get the newly inserted ID — MAX is not safe under concurrency.
Set AUTO_INCREMENT = N above your current max when importing data to avoid collisions.
On MySQL 8.0 the counter survives restarts; on 5.7 it resets to MAX(id)+1 after restart — account for this if you allow deletes of high-IDs.
For distributed/microservice architectures consider ULIDs or ordered UUIDs instead of AUTO_INCREMENT.