Why MySQL Auto‑Increment IDs Skip Numbers and How to Prevent Gaps

When a MySQL table has an auto‑increment primary key, inserting rows normally generates sequential IDs, but in practice the IDs can be non‑continuous. The following factors cause gaps:

1. Deleting rows

Deleted rows do not free their auto‑increment values; the next insert uses the next available number, creating a gap.

2. Manually specifying the primary key

If you insert a row with an explicit primary‑key value instead of letting MySQL generate it, the auto‑increment sequence is broken and gaps appear.

3. Transaction rollback

When an insert is rolled back, the allocated auto‑increment value is not reclaimed, so subsequent inserts continue from the higher number.

4. Unique‑key conflicts

If an insert fails due to a unique‑key violation, the auto‑increment value consumed for that attempt is not rolled back, leading to gaps.

5. Bulk insert allocation strategy

During bulk inserts MySQL allocates IDs in batches that double each time (1, then 2, then 4, …). If fewer rows are actually inserted than IDs allocated, the unused IDs are discarded, causing non‑sequential IDs.

For example, inserting five rows starting from ID 1 results in three allocation rounds: first ID = 1, second IDs = 2, 3, third IDs = 4, 5, 6, 7. Only IDs 1‑5 are used; IDs 6‑7 are wasted, so the next insert starts at ID 8, creating a gap.