11.1 Introduction
A
sequence is a set
of integers 1, 2, 3, ... that are generated in order on demand.
Sequences are frequently used 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. It covers the following topics:
Using AUTO_INCREMENT columns to create sequences. 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_INCREMENT 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 necessary to
determine the sequence value for a just-inserted record. A web
application may need to redisplay to a user the contents of a record
created from the contents of a form just submitted by the user. Or
the value may need to be retrieved so it can be stored as part of
other records in a related table.
Resequencing techniques. This section describes how to renumber a sequence that has holes in
it due to record deletions�and also discusses reasons to avoid
resequencing. Other topics include starting sequences at values other
than 1 and adding a sequence column to a table that
doesn't have one.
Using an AUTO_INCREMENT column to create multiple sequences. In many cases, the AUTO_INCREMENT column in a
table is independent of other columns and its values increment
throughout the table in a single monotonic sequence. However, if you
create a multiple-column index that contains an
AUTO_INCREMENT column, you can use it to generate
multiple sequences. For example, if you run a bulletin board that
categorizes messages into topics, you can number messages
sequentially within each topic by tying an
AUTO_INCREMENT column to a topic indicator column.
Managing multiple simultaneous AUTO_INCREMENT values. Special care is necessary when you need to keep track of multiple
sequence values. This can occur when you issue a set of statements
that affect a single table, or when creating records in multiple
tables that each have an AUTO_INCREMENT column.
This section describes what to do in these cases.
Using single-row sequence generators. Sequences also can be used as
counters. For example, if you serve banner ads on your web site, you
might increment a counter for each impression (that is, for each time
you serve an ad). The counts for a given ad form a sequence, but
because the count itself is the only value of interest, there is no
need to generate a new row to record each impression. MySQL provides
a solution for this problem, too, using a mechanism that allows a
sequence to be easily generated within a single table row over time.
To store multiple counters in the table, add a column that identifies
the counter uniquely. For example, you can have an arbitrary number
of ad impression counters in a table. Each row in the table
identifies a specific banner ad, and the counter in each row
increments independently of the others. The same mechanism also
allows creation of sequences that increase by values other than one,
by non-uniform values, or even by negative increments.
Numbering query output rows sequentially. This section suggests ways to generate display-only sequences for the
purpose of numbering the rows of output from a query.
The engines for most database
systems provide sequence generation capabilities, though the
implementations tend to be engine-dependent. That's
true for MySQL as well, so the material in this section is almost
completely MySQL-specific, even at the SQL level. In other words, the
SQL for generating sequences is itself non-portable, even if you use
an API like DBI or JDBC that provides an abstraction layer. Abstract
interfaces may help you process SQL statements portably, but they
don't make nonportable SQL portable.
|
|
No comments:
Post a Comment