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
Check whether 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 data type is signed, make it
UNSIGNED
, and you’ll double the range of available values. Suppose that you have anid
column that currently is defined like this:id MEDIUMINT NOT NULL AUTO_INCREMENT
The upper range of a signed
MEDIUMINT
column is 8,388,607. This can be increased to 16,777,215 by making the columnUNSIGNED
withALTER
TABLE
:ALTER TABLE
tbl_name
MODIFY id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT;If your column is already
UNSIGNED
and it is not already the largest integer type (BIGINT
), converting it to a larger type increases its range. You can useALTER
TABLE
for this, too. For example, theid
column in the previous example can be converted fromMEDIUMINT
toBIGINT
like so:ALTER TABLE
tbl_name
MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
Choosing the Data Type for a Sequence Column includes a table that shows the ranges for each integer data type. You might find it helpful in assessing which type to use.
Get MySQL Cookbook, 2nd Edition 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.