Extending the Range of a Sequence Column

11.8.1 Problem

You want to avoid resequencing a column, but you're running out of room for new sequence numbers.

11.8.2 Solution

See if you can make the column UNSIGNED or change the column to use a larger integer type.

11.8.3 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 UNSIGNED and you'll double the range of available values. Suppose 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 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 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 TABLE tbl_name MODIFY 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.

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

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net