11.4.1 Problem
You want to know more about how to define a sequence column.
11.4.2 Solution
Use the guidelines given here.
11.4.3 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:
Column type |
Maximum unsigned value |
---|---|
TINYINT |
255 |
SMALLINT |
65,535 |
MEDIUMINT |
16,777,215 |
INT |
4,294,967,295 |
BIGINT |
18,446,744,073,709,551,615 |
Sometimes people omit UNSIGNED so that they can create records that contain negative numbers in the sequence column. (Using -1 to signify "has no ID" would be an instance of this.) MySQL makes no guarantees about how negative numbers will be treated, so you're playing with fire if you try to use them in an AUTO_INCREMENT column. For example, if you resequence the column, you'll find that all your negative values get turned into regular (positive) sequence numbers.
id INT UNSIGNED NOT NULL AUTO_INCREMENT, UNIQUE (id)
If the AUTO_INCREMENT column is the only column in the PRIMARY KEY or UNIQUE index, you can declare it as such in the column definition rather than in a separate clause. For example, these definitions are equivalent:
id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
As are these:
id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE id INT UNSIGNED NOT NULL AUTO_INCREMENT, UNIQUE (id)
Using a separate clause to specify the index helps to emphasize that it's not, strictly speaking, part of the column definition. (If you read through Chapter 8, you'll notice that modifying a column's indexes is discussed separately from changing the definition of the column itself.)
When creating a table that contains an AUTO_INCREMENT column, it's also important to consider the table type (MyISAM, InnoDB, and so forth). The type affects behaviors such as reuse of values that are deleted from the top of the sequence, and whether or not you can set the initial sequence value. In general, MyISAM is the best type for tables that contain AUTO_INCREMENT columns, because it offers the most features for sequence management. This will become apparent as you continue through the chapter.
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