Database Reference
In-Depth Information
assigned sequence numbers in a particular order. For example, you may want the se‐
quence to correspond to the order in which rows were created, as indicated by a
TIME
STAMP
column. To assign numbers in a particular order, use this procedure:
1. Create an empty clone of the table (see
Recipe 4.1
).
2. Copy rows from the original into the clone using
INSERT
INTO
…
SELECT
. Copy all
columns except the
AUTO_INCREMENT
column, using an
ORDER
BY
clause to specify
the order in which rows are copied (and thus the order in which MySQL assigns
numbers to the
AUTO_INCREMENT
column).
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 is more efficient to
create the new table initially with no indexes except the one on the
AUTO_INCRE
MENT
column. Then copy the original table into the new table and use
ALTER
TABLE
to 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. Use
TRUNCATE
TABLE
on the original table to empty it; this also resets the sequence
counter to 1.
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. MySQL
assigns sequence values to the
AUTO_INCREMENT
column.
13.9. Sequencing an Unsequenced Table
Problem
You forgot to include a sequence column when you created a table. Is it too late to
sequence the table rows?
Solution
No. Add an
AUTO_INCREMENT
column using
ALTER
TABLE
; MySQL creates the column
and numbers its rows.