11.10 Reusing Values at the Top of a Sequence
11.10.1 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?
11.10.2 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.
11.10.3 Discussion
If you have removed records only from the top of the sequence, those
that remain will still be in order with no gaps. (For example if you
have records numbered 1 to 100 and you remove records 91 to 100, the
remaining records 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 ISAM or BDB tables, that's the default behavior
anyway, so the deleted values will be reused with no additional
action on your part. For MyISAM or InnoDB tables, issue the following
statement:
ALTER TABLE tbl_name AUTO_INCREMENT = 1
This causes MySQL to reset the sequence counter down as far as it can
for creating new records 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 you have
a table with sequence values from 1 to 10 and then delete the records
for values 3, 4, 5, 9, and 10. The maximum remaining value is 8, so
if you use ALTER TABLE to reset
the sequence counter, the next record will be given a value of 9, not
3. To resequence a table and eliminate the gaps as well, see Recipe 11.9.
|
