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.
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 might 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 (see Cloning a Table).
Copy rows from the original into the clone using INSERT
INTO
... SELECT
. Copy all columns except the
sequence column, using an ORDER
BY
clause 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 a large MyISAM table and has multiple
indexes, it will be more efficient to create the new table
initially with no indexes except the one on the AUTO_INCREMENT
column. Then copy the
original table into the new table and add the remaining indexes
afterward.
An alternative procedure:
Create a new table that contains all the columns of the
original table except the AUTO_INCREMENT
column.
Use INSERT
INTO
... SELECT
to copy the non-AUTO_INCREMENT
columns from the original
table into the new table.
Delete the rows from the original table, and reset the sequence counter to 1 if necessary.
Copy rows from the new table back to the original table,
using an ORDER
BY
clause to sort rows into the order in
which you want sequence numbers assigned. MyISAM will assign
sequence values to the AUTO_INCREMENT
column.
18.216.255.250