Database Reference
In-Depth Information
13.6. 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 it 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
to double the range of available values.
Suppose that an
id
column currently is defined like this:
id MEDIUMINT NOT NULL AUTO_INCREMENT
The upper range of a signed
MEDIUMINT
column is 8,388,607. To increase this to
16,777,215, make the column
UNSIGNED
with
ALTER
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. Use
ALTER
TABLE
for this,
too. Convert the
id
column in the previous example from
MEDIUMINT
to
BIGINT
like
so:
ALTER
TABLE
tbl_name
MODIFY
id
BIGINT
UNSIGNED
NOT
NULL
AUTO_INCREMENT
;
Recipe 13.2
shows the ranges for each integer data type, which can help you choose an
appropriate type.
13.7. Reusing Values at the Top of a Sequence
Problem
You've deleted rows at the top end of your sequence. Can you avoid resequencing the
column but still reuse the values that have been deleted?
