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