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 an id
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 column UNSIGNED
with ALTER
TABLE
:
ALTER TABLEtbl_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 use
ALTER
TABLE
for this, too. For example, the
id
column in the previous
example can be converted from MEDIUMINT
to BIGINT
like so:
ALTER TABLEtbl_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.
3.142.12.207