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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.