MySQL AUTO_INCREMENT
debt(d7/e5/b3/t5)
Closest to 'only careful code review or runtime testing' (d7). The detection_hints indicate no automated detection ('automated: no'), and the code pattern (signed INT on high-volume tables) requires a human reviewer to recognize the approaching range limit or gap-reliance bug. No linter or SAST tool is listed. The INT vs INT UNSIGNED mistake and gap-assumption bugs are silent in normal operation until the table nears its limit or a concurrency issue surfaces.
Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix is a simple column type swap, but the common_mistakes reveal that switching from INT to BIGINT requires an ALTER TABLE (which locks the table on large datasets), and fixing application code that incorrectly assumes sequential/max IDs touches multiple query sites and possibly business logic. Not a single-line patch, but not a full architectural rework either.
Closest to 'localised tax' (b3). AUTO_INCREMENT applies to a specific table's primary key column. The choice of INT vs INT UNSIGNED vs BIGINT is localized to that table's schema definition. While it can affect application code that reads IDs, the structural burden is mostly confined to schema and any queries directly referencing the column type limit, not a cross-cutting concern.
Closest to 'notable trap (a documented gotcha most devs eventually learn)' (t5). The misconception is explicitly documented: developers assume AUTO_INCREMENT IDs are sequential with no gaps, but rolled-back transactions, failed inserts, and server restarts all create gaps. This is a well-known gotcha that competent developers eventually learn but commonly get wrong initially. The INT vs INT UNSIGNED halving of range is an additional subtle trap.
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 UNSIGNEDCREATE 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();