MySQL Cookbook, 2nd Edition

The Effect of Row Deletions on Sequence Generation

Problem

You want to know what happens to a sequence when you delete rows from a table that contains an AUTO_INCREMENT column.

Solution

It depends on which rows you delete and on the storage engine.

Discussion

We have thus far considered how sequence values in an AUTO_INCREMENT column are generated for circumstances where rows are only added to a table. But it’s unrealistic to assume that rows will never be deleted. What happens to the sequence then?

Refer again to Junior’s bug-collection project, for which you currently have an insect table that looks like this:

mysql>SELECT * FROM insect ORDER BY id;
+----+-------------------+------------+------------+
| id | name              | date       | origin     |
+----+-------------------+------------+------------+
|  1 | housefly          | 2006-09-10 | kitchen    |
|  2 | millipede         | 2006-09-10 | driveway   |
|  3 | grasshopper       | 2006-09-10 | front yard |
|  4 | stink bug         | 2006-09-10 | front yard |
|  5 | cabbage butterfly | 2006-09-10 | garden     |
|  6 | ant               | 2006-09-10 | back yard  |
|  7 | ant               | 2006-09-10 | back yard  |
|  8 | millbug           | 2006-09-10 | under rock |
+----+-------------------+------------+------------+

That’s about to change because after Junior remembers to bring home the written instructions for the project, you read through them and discover two things that affect the insect table’s contents:

• Specimens should include only insects, not other insect-like creatures such as millipedes and millbugs.

• The purpose of the project ...