MySQL Cookbook

Renumbering an Existing Sequence

Problem

You’re determined to resequence a column, despite my advice not to.

Solution

Drop the column from the table. Then put it back. MySQL will renumber the values in the column in unbroken sequence.

Discussion

If you determine that resequencing an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the column from the table, then add it again. The following example shows how to renumber the id values in the insect table using this technique:

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);

The first ALTER TABLE statement gets rid of the id column (and as a result also drops the PRIMARY KEY, because the column to which it refers is no longer present.) The second statement restores the column to the table and establishes it as the PRIMARY KEY. (The FIRST keyword places the column first in the table, which is where it was originally. Normally, ADD puts columns at the end of the table.) When you add an AUTO_INCREMENT column to a table, MySQL automatically numbers all the rows consecutively, so the resulting contents of the insect table look like this:

mysql> SELECT * FROM insect ORDER BY id; +----+-------------------+------------+------------+ | id | name | date | origin | +----+-------------------+------------+------------+ | 1 | housefly | 2001-09-10 | kitchen | | 2 | grasshopper | 2001-09-10 | front yard | | 3 | stink bug | 2001-09-10 | front yard ...