11.11 Ensuring That Rows Are Renumbered in a Particular Order
11.11.1 Problem
You resequenced a column, but MySQL
didn't number the rows the way you want.
11.11.2 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.
11.11.3 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
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 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_INCREMENT column. Then copy
the original table into the new table and add the remaining indexes
afterward.
An alternate 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.
For information on creating a clone table, see Recipe 3.26. If you're using one of these
procedures from within a program that doesn't
necessarily have any prior knowledge about the structure of the
table, use the table metadata to get a list of column names and to
determine which column has the AUTO_INCREMENT
attribute. (See Recipe 9.6.)
|
