Recipe 11.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 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 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:

  1. Create an empty clone of the table (see Section 4.1).

  2. 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).

  3. Drop the original table and rename the clone to have the original table's name.

  4. 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:

  1. Create a new table that contains all the columns of the original table except the AUTO_INCREMENT column.

  2. Use INSERT INTO ... SELECT to copy the non-AUTO_INCREMENT columns from the original table into the new table.

  3. Delete the rows from the original table, and reset the sequence counter to 1 if necessary.

  4. 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.




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net