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 as it performs the operation. Then the rows
will be numbered according to the sort order.
Discussion
When you resequence an
AUTO_INCREMENT column, MySQL is free to pick the
rows from the table in any order, so it won’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 may have an
application for which it’s important that the rows
be assigned sequence numbers in a particular order. For example, you
may want the sequence to correspond to the order in which rows were
created, as indicated by a TIMESTAMP column. To
assign numbers in a particular order, use this procedure:
Create an empty clone of the table.
Copy rows from the original into the clone using
INSERTINTO...SELECT. Copy all columns except the sequence column, using anORDERBYclause to specify the order in which rows are copied (and thus assigned sequence numbers).Drop the original table and rename the clone to have the original table’s name.
If the table is large and has multiple indexes, it will be more efficient to create the new table initially with no indexes except the one on the
AUTO_INCREMENTcolumn. Then copy the original table into the ...