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

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,

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

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois © 2008-2020.
If you may any questions please contact us: