Determining Whether to Resequence a Column

11.7.1 Problem

You have gaps in a sequence column and you're wondering whether you should try to resequence it.

11.7.2 Solution

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

11.7.3 Discussion

If you insert records 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 records, 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 records shown in the preceding section on retrieving sequence values):

mysql> SELECT * FROM insect ORDER BY id; +----+-------------------+------------+------------+ | id | name | date | origin | +----+-------------------+------------+------------+ | 1 | housefly | 2001-09-10 | kitchen | | 3 | grasshopper | 2001-09-10 | front yard | | 4 | stink bug | 2001-09-10 | front yard | | 5 | cabbage butterfly | 2001-09-10 | garden | | 6 | ant | 2001-09-10 | back yard | | 9 | cricket | 2001-09-11 | basement | | 10 | moth | 2001-09-14 | windowsill | +----+-------------------+------------+------------+

MySQL won't attempt to eliminate these gaps by filling in the unused values when you insert new records. People who don't like this behavior tend to resequence AUTO_INCREMENT columns periodically to eliminate the holes. The next few sections show how to do that. It's also possible to add a sequence column to a table that doesn't currently have one, force deleted values at the top of a sequence to be reused, and to specify an initial sequence value when creating or resequencing a table.

11.7.4 Reasons to Avoid Resequencing

Before deciding to resequence an AUTO_INCREMENT column, consider whether you really want or need to do so. It's unnecessary in most cases. In fact, renumbering a sequence sometimes can cause you real problems. For example, you should not resequence a column containing values that are referenced by another table. Renumbering the values destroys their correspondence to values in the other table, making it impossible to properly relate records in the two tables to each other.

Reasons that people have for resequencing include the following:

Категории