Generating and Using Sequences - MySQL - page 446

Managing multiple simultaneous sequences
Special care is necessary when you need to keep track of multiple sequence values,
such as when you create rows in multiple tables that each have an AUTO_INCRE
MENT column.
Using single-row sequence generators
Sequences can be used as counters. For example, to count votes in a poll, you might
increment a counter each time a candidate receives a vote. The counts for a given
candidate 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 vote. MySQL provides a
solution for this problem using a mechanism that enables a sequence to be easily
generated within a single table row over time. To store multiple counters in the
table, use a column that identifies each counter uniquely. The same mechanism also
enables creation of sequences that increase by values other than one or by nonuni‐
form values.
The engines for most database systems provide sequence-generation capabilities, al‐
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 nonportable, even
if you use an API such as DBI or JDBC that provides an abstraction layer. Abstract
interfaces may help you process SQL statements portably, but they don't make non‐
portable SQL portable.
Scripts related to the examples shown in this chapter are located in the sequences di‐
rectory of the recipes distribution. For scripts that create tables used here, look in the
tables directory.
13.1. Creating a Sequence Column and Generating
Sequence Values
Problem
A table must include a column containing unique IDs.
Solution
Use an AUTO_INCREMENT column to generate a sequence.
Discussion
This section provides the essential background on using AUTO_INCREMENT columns,
beginning with an example that demonstrates the sequence-generation mechanism. The
illustration centers around a bug-collection scenario: your eight-year-old son Junior is