Database Reference
In-Depth Information
CHAPTER 13
Generating and Using Sequences
13.0. Introduction
A sequence is a set of integers (1, 2, 3, …) generated in order on demand. Sequences see
frequent use in databases because many applications require each row in a table to
contain a unique value, and sequences provide an easy way to generate them. This
chapter describes how to use sequences in MySQL:
Using
AUTO_INCREMENT
columns
The
AUTO_INCREMENT
column is MySQL's mechanism for generating a sequence
over a set of rows. Each time you create a row in a table that contains an
AUTO_IN
CREMENT
column, MySQL automatically generates the next value in the sequence as
the column's value. This value serves as a unique identifier, making sequences an
easy way to create items such as customer ID numbers, shipping package waybill
numbers, invoice or purchase order numbers, bug report IDs, ticket numbers, or
product serial numbers.
Retrieving sequence values
For many applications, it's not enough just to create sequence values. It's also nec‐
essary to determine the sequence value for a just-inserted row. A web application
may need to redisplay to a user the contents of a row created from the contents of
a form just submitted by the user. The value may need to be retrieved so it can be
stored in rows of a related table.
Resequencing techniques
It's possible to renumber a sequence that has holes in it due to row deletions, reuse
deleted values at the top of a sequence, or add a sequence column to a table that has
none.
