MySQL Cookbook, 2nd Edition

Renumbering an Existing Sequence

Problem

You have gaps in a sequence column, and you want to resequence it.

Solution

Don’t bother. Or at least don’t do so without a good reason, of which there are very few.

Discussion

If you insert rows into a table that has an AUTO_INCREMENT column and never delete any of them, values in the column form an unbroken sequence. But if you delete rows, the sequence begins to have holes in it. For example, Junior’s insect table currently looks something like this, with gaps in the sequence (assuming that you’ve inserted the cricket and moth rows shown in the preceding section on retrieving sequence values):

mysql>SELECT * FROM insect ORDER BY id;
+----+-------------------+------------+------------+
| id | name              | date       | origin     |
+----+-------------------+------------+------------+
|  1 | housefly          | 2006-09-10 | kitchen    |
|  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  |
|  9 | cricket           | 2006-09-11 | basement   |
| 10 | moth              | 2006-09-14 | windowsill |
+----+-------------------+------------+------------+

MySQL won’t attempt to eliminate these gaps by filling in the unused values when you insert new rows. People who don’t like this behavior tend to resequence AUTO_INCREMENT columns periodically to eliminate the holes. The next few recipes show how to do that. It’s also possible to extend the range of an existing sequence, add a sequence column ...