MySQL Cookbook, 2nd Edition

Choosing the Data Type for a Sequence Column

Problem

You want to know more about how to define a sequence column.

Solution

Use the guidelines given here.

Discussion

You should follow certain guidelines when creating an AUTO_INCREMENT column. As an illustration, consider how the id column in the insect table was declared:

id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)

The AUTO_INCREMENT keyword informs MySQL that it should generate successive sequence numbers for the column’s values, but the other information is important, too:

• INT is the column’s base data type. You need not necessarily useINT, but the column must be one of the integer types:TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT. It’s important to remember that AUTO_INCREMENT is a column attribute that should be applied only to integer types.

• The column is declared as UNSIGNED to disallow negative values. This is not a requirement for an AUTO_INCREMENT column. However, there is no reason to allow negative values because sequences consist only of positive integers (normally beginning at 1). Furthermore, not declaring the column to be UNSIGNED cuts the range of your sequence in half. For example, TINYINT has a range of –128 to 127. Sequences include only positive values, so the range of a TINYINT sequence would be 1 to 127. The range of a TINYINT UNSIGNED column is 0 to 255, which increases the upper end of the sequence to 255. The maximum sequence value is determined by the specific integer type used, so you should choose ...