MySQL Cookbook, 2nd Edition

Reusing Values at the Top of a Sequence

Problem

You’ve deleted rows at the top end of your sequence. Can you avoid resequencing the column but still reuse the values that have been deleted?

Solution

Yes, use ALTER TABLE to reset the sequence counter. MySQL will generate new sequence numbers beginning with the value that is one larger than the current maximum in the table.

Discussion

If you have removed rows only from the top of the sequence, those that remain will still be in order with no gaps. (For example, if you have rows numbered 1 to 100 and you remove the rows with numbers 91 to 100, the remaining rows are still in unbroken sequence from 1 to 90.) In this special case, it’s unnecessary to renumber the column. Instead, just tell MySQL to resume the sequence beginning with the value one larger that the highest existing sequence number. For BDB tables, that’s the default behavior anyway, so the deleted values are reused with no additional action on your part. For MyISAM or InnoDB tables, issue the following statement:

ALTER TABLEtbl_name AUTO_INCREMENT = 1;

This causes MySQL to reset the sequence counter down as far as it can for creating new rows in the future.

You can use ALTER TABLE to reset the sequence counter if a sequence column contains gaps in the middle, but doing so still will reuse only values deleted from the top of the sequence. It will not eliminate the gaps. Suppose that you have a table with sequence values from 1 to 10 and then delete the rows for values 3, 4, 5, 9, ...