MySQL AUTO_INCREMENT

Also Known As

TL;DR

Explanation

AUTO_INCREMENT columns must be an integer type and part of a key (usually PRIMARY KEY). The counter increments even on rolled-back transactions — gaps in the sequence are normal and expected. The current counter value is stored in InnoDB's in-memory state and resets on server restart for pre-8.0 MySQL (fixed in 8.0 via redo log persistence). Use UNSIGNED INT (max ~4.2B) or BIGINT UNSIGNED (max ~18.4 quintillion) depending on expected row count.

Watch Out

Common Misconception

Why It Matters

Common Mistakes

• Using INT (signed) instead of INT UNSIGNED — halves the available range for no benefit.
• Writing code that assumes the last inserted ID equals max(id) — concurrent inserts break this assumption.
• Not switching to BIGINT when an INT column approaches its limit — requires ALTER TABLE which locks the table.

Avoid When

• Do not write application logic that assumes sequential, gap-free IDs — rolled-back transactions create gaps.
• Do not use AUTO_INCREMENT when you need a globally unique ID across distributed systems — use UUID instead.

When To Use

• Use INT UNSIGNED AUTO_INCREMENT for tables expected to stay under 4.2 billion rows.
• Use BIGINT UNSIGNED for high-volume tables like events, logs, or analytics.

Code Examples

-- INT can overflow on high-volume tables (~2.1B rows with signed, ~4.2B unsigned)
id INT AUTO_INCREMENT PRIMARY KEY
-- If you expect >4.2B rows, use BIGINT UNSIGNED

CREATE TABLE users (
    id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name  VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

-- Get last inserted ID via PDO
$pdo->prepare('INSERT INTO users (email, name) VALUES (?, ?)')
    ->execute([$email, $name]);
$userId = (int) $pdo->lastInsertId();

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

Tags

Related Terms