Understanding MySQL InnoDB Auto‑Increment: Deletions, Restarts, and Manual IDs

The following questions are based on the InnoDB storage engine.

1. What ID is assigned after deleting the record with the highest ID?

Given a table with IDs 1, 2, 3, if the record with ID 3 is deleted, the next inserted record receives ID 4.

create table tb0(id int unsigned auto_increment primary key);
insert into tb0 values(null);
insert into tb0 values(null);
insert into tb0 values(null);
delete from tb0 where id=3;
show create table tb0;

The auto‑increment value becomes 4; deleting the maximum ID does not affect the next auto‑increment value.

2. Where does the auto‑increment start after restarting MySQL?

With IDs 1, 2, 3 and ID 3 deleted, many expect the next ID to be 4, but after a MySQL restart it starts from 3.

InnoDB stores the auto‑increment counter in memory, not in the data file. After a restart, MySQL sets the counter to current maximum ID + 1 .

create table tb1(id int unsigned auto_increment primary key);
insert into tb1 values(null);
insert into tb1 values(null);
insert into tb1 values(null);
delete from tb1 where id=3;
show create table tb1;

After restarting MySQL, the auto‑increment value is reset to 3.

3. What is the next auto‑increment value after manually inserting a larger ID?

If the current auto‑increment is 4 and a record is manually inserted with ID 10, the subsequent auto‑incremented insert will use ID 11.

Auto‑increment value = current maximum ID + 1. The new ID is calculated at insert time.

create table tb2(id int unsigned auto_increment primary key);
insert into tb2 values(null);
insert into tb2 values(4294967000);
show create table tb2;

The auto‑increment value becomes 4294967001.

4. What happens when the auto‑increment value is exhausted?

The maximum value of an unsigned int is 4294967295; once reached, the auto‑increment stops and further inserts cause a duplicate‑key error. Duplicate entry '4294967295' for key 'PRIMARY' For tables with frequent inserts and deletes, using bigint is recommended.

int is 4 bytes; signed range: -2147483648 to 2147483647; unsigned range: 0 to 4294967295. bigint is 8 bytes; signed range: -9223372036854775808 to 9223372036854775807; unsigned range: 0 to 18446744073709551615.

Summary

When inserting a new record, the auto‑increment value is calculated as the maximum existing ID plus one, regardless of whether the ID is generated automatically or set manually.

Deleting the maximum ID does not affect the auto‑increment counter, but a MySQL restart recomputes it as max ID + 1 because the counter is kept in memory.

Once the auto‑increment limit is reached, the counter stops and inserts fail; switching to bigint mitigates this issue.