Database Reference
In-Depth Information
Solution
Yes. Use
ALTER
TABLE
to reset the sequence counter. New sequence numbers will begin
with the value 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 are still
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, tell
MySQL to resume the sequence beginning with the value one larger than the highest
existing sequence number by executing this statement, which causes MySQL to reset
the sequence counter down as far as it can for new rows:
ALTER
TABLE
tbl_name
AUTO_INCREMENT
=
1
;
You can use
ALTER
TABLE
to reset the sequence counter if a sequence column contains
gaps in the middle, but doing so still reuses only values deleted from the top of the
sequence. It does not eliminate the gaps. Suppose that a table contains sequence values
from 1 to 10, from which you delete the rows 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
row is given a value of 9, not 3. To resequence a table to eliminate the gaps, see
Recipe 13.5
.
13.8. Ensuring That Rows Are Renumbered in a Particular
Order
Problem
You resequenced a column, but MySQL didn't number the rows the way you want.
Solution
Select the rows into another table, using an
ORDER
BY
clause to place them in the order
you want, and let MySQL number them according to the sort order as it performs the
operation.
Discussion
When you resequence an
AUTO_INCREMENT
column, MySQL is free to pick the rows from
the table in any order, so it doesn't necessarily renumber them in the order that you
expect. This doesn't matter at all if your only requirement is that each row have a unique
identifier. But you might have an application for which it's important that the rows be
