Extending the Range of a Sequence Column
Problem
You want to avoid resequencing a column, but you’re running out of room for new sequence numbers.
Solution
See if you can make the column UNSIGNED or change
the column to use a larger integer type.
Discussion
Resequencing an AUTO_INCREMENT column changes the
contents of potentially every row in the table. It’s
often possible to avoid this by extending the range of the column,
which changes the table’s structure rather than its
contents:
If the column type is signed, make it
UNSIGNEDand you’ll double the range of available values. Suppose you have anidcolumn that currently is defined like this:id MEDIUMINT NOT NULL AUTO_INCREMENT
The upper range of a signed
MEDIUMINTcolumn is 8,388,607. This can be increased to 16,777,215 by making the columnUNSIGNEDwithALTERTABLE:ALTER TABLE
tbl_nameMODIFY id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT;If your column is already
UNSIGNEDand it is not already the largest integer type (BIGINT), converting it to a larger type increases its range. You can useALTERTABLEfor this, too. For example, theidcolumn in the previous example can be converted fromMEDIUMINTtoBIGINTlike so:ALTER TABLE
tbl_nameMODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
Recipe 11.4 includes a table that shows the ranges for each integer type, which you may find helpful in assessing which type to use.