Renumbering an Existing Sequence

11.9.1 Problem

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

11.9.2 Solution

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

11.9.3 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 |
| 4 | cabbage butterfly | 2001-09-10 | garden |
| 5 | ant | 2001-09-10 | back yard |
| 6 | cricket | 2001-09-11 | basement |
| 7 | moth | 2001-09-14 | windowsill |
+----+-------------------+------------+------------+

One problem with resequencing a column using separate ALTER TABLE statements is that the table will be without that column for the interval between the two operations. This may cause difficulties for other clients that try to access the table during that time. To prevent this from happening, perform both operations with a single ALTER TABLE statement:

mysql> ALTER TABLE insect
 -> DROP id,
 -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
 -> AUTO_INCREMENT = 1;

MySQL permits multiple actions to be done with ALTER TABLE (something not true for all database systems). However, notice that this multiple-action statement is not simply a concatenation of the two single-action ALTER TABLE statements. It differs in two ways:

• It's unnecessary to reestablish the PRIMARY KEY, because MySQL doesn't drop it unless the indexed column is missing after all the actions specified in the ALTER TABLE statement have been performed.
• The AUTO_INCREMENT clause ensures that MySQL begins the sequence with the value 1. This is actually necessary only prior to MySQL 3.23.39, due to a bug in which MySQL fails to reset the sequence counter when dropping and adding a column in a single ALTER TABLE statement. (For example, if you had a table containing values 8, 12, and 14, the new sequence would be numbered 15, 16, and 17 without the AUTO_INCREMENT clause.)