You need to have sequencing behavior that is more complex than a single sequence of values. You need to tie different sequences to the values in other columns of the table.
Link the AUTO_INCREMENT
column to those other columns, making them all part of the same
index.
When an AUTO_INCREMENT
column
is the only column in a
PRIMARY
KEY
or
UNIQUE
index, it
generates a single sequence 1, 2, 3, ... in which successive values
increase by one each time you add a row, regardless of the contents of
the rest of the row. For MyISAM or BDB tables, it’s possible to create
an index that combines an AUTO_INCREMENT
column with other columns to
generate multiple sequences within a single table.
Here’s how it works: let’s say that Junior develops such a passion for bug collecting that he decides to keep it up even after the school project has been completed—except that when freed from the constraints of the teacher’s instructions, he’s perfectly content to include insect-like bugs such as millipedes, and even to collect multiple instances of any given creature. Junior happily goes outside and collects more specimens over the next few days:
Name | Date | Origin |
---|---|---|
ant | 2006-10-07 | kitchen |
millipede | 2006-10-07 | basement |
beetle | 2006-10-07 | basement |
ant | 2006-10-07 | front yard |
ant | 2006-10-07 | front yard |
honeybee | 2006-10-08 | back yard |
cricket | 2006-10-08 | garage |
beetle | 2006-10-08 | front yard |
termite | 2006-10-09 | kitchen woodwork |
cricket | 2006-10-10 | basement |
termite | 2006-10-11 | bathroom woodwork |
honeybee | 2006-10-11 | garden |
cricket | 2006-10-11 | garden |
ant | 2006-10-11 | garden |
After recording this information, he’s ready to enter it into
the database but wants to number each kind of bug separately (ant 1,
ant 2, ..., beetle 1, beetle 2, ..., cricket 1, cricket 2, and so
forth). To that end, you look over the data (noting with some alarm
Junior’s discovery of termites in the house and making a mental note
to call the exterminator), and then design a bug
table for Junior that looks like
this:
CREATE TABLE bug ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(30) NOT NULL, # type of bug date DATE NOT NULL, # date collected origin VARCHAR(30) NOT NULL, # where collected PRIMARY KEY (name, id) );
This is very similar to the insect
table, but has one significant
difference: the PRIMARY
KEY
comprises two columns, not one. As a
result, the id
column will behave
somewhat differently than for the insect
table. If the new set of specimens is
entered into the bug
table in the
order in which Junior wrote them down, here’s what the resulting table
looks like:
mysql>SELECT * FROM bug;
+----+-----------+------------+-------------------+
| id | name | date | origin |
+----+-----------+------------+-------------------+
| 1 | ant | 2006-10-07 | kitchen |
| 1 | millipede | 2006-10-07 | basement |
| 1 | beetle | 2006-10-07 | basement |
| 2 | ant | 2006-10-07 | front yard |
| 3 | ant | 2006-10-07 | front yard |
| 1 | honeybee | 2006-10-08 | back yard |
| 1 | cricket | 2006-10-08 | garage |
| 2 | beetle | 2006-10-08 | front yard |
| 1 | termite | 2006-10-09 | kitchen woodwork |
| 2 | cricket | 2006-10-10 | basement |
| 2 | termite | 2006-10-11 | bathroom woodwork |
| 2 | honeybee | 2006-10-11 | garden |
| 3 | cricket | 2006-10-11 | garden |
| 4 | ant | 2006-10-11 | garden |
+----+-----------+------------+-------------------+
Looking at the table that way, it appears that the id
values are being assigned at random—but
they’re not. Sort the table by name
and id
, and it becomes clear how
MySQL assigns the values. Specifically, MySQL creates a separate
id
sequence for each distinct
name
value:
mysql>SELECT * FROM bug ORDER BY name, id;
+----+-----------+------------+-------------------+
| id | name | date | origin |
+----+-----------+------------+-------------------+
| 1 | ant | 2006-10-07 | kitchen |
| 2 | ant | 2006-10-07 | front yard |
| 3 | ant | 2006-10-07 | front yard |
| 4 | ant | 2006-10-11 | garden |
| 1 | beetle | 2006-10-07 | basement |
| 2 | beetle | 2006-10-08 | front yard |
| 1 | cricket | 2006-10-08 | garage |
| 2 | cricket | 2006-10-10 | basement |
| 3 | cricket | 2006-10-11 | garden |
| 1 | honeybee | 2006-10-08 | back yard |
| 2 | honeybee | 2006-10-11 | garden |
| 1 | millipede | 2006-10-07 | basement |
| 1 | termite | 2006-10-09 | kitchen woodwork |
| 2 | termite | 2006-10-11 | bathroom woodwork |
+----+-----------+------------+-------------------+
When you create a multiple-column AUTO_INCREMENT
index, note the following
points:
The order in which the CREATE
TABLE
statement defines the indexed
columns does not matter. What is significant
is the order in which the index definition names the columns. The
AUTO_INCREMENT
column must be
named last, or the multiple-sequence mechanism will not
work.
A PRIMARY
KEY
cannot contain NULL
values, but a UNIQUE
index can. If any of the
non-AUTO_INCREMENT
columns to
be indexed might contain NULL
values, you should create a UNIQUE
index rather than a PRIMARY
KEY
.
For the bug
table, the
AUTO_INCREMENT
index has two
columns. The same technique can be extended to more than two columns,
but the basic concept is the same: for an n
-column
index where the last one is an AUTO_INCREMENT
column, MySQL generates an
independent sequence for each unique combination of values in the
non-AUTO_INCREMENT
columns.
MySQL’s mechanism for multiple-column sequences can be easier to
use than logically equivalent single-column values. Recall that in
Sorting by Fixed-Length Substrings, we used a housewares
table that contained rows with
three-part product ID values composed of a three-character category
abbreviation, a five-digit serial number, and a two-character code
indicating country of manufacture:
+------------+------------------+ | id | description | +------------+------------------+ | DIN40672US | dining table | | KIT00372UK | garbage disposal | | KIT01729JP | microwave oven | | BED00038SG | bedside lamp | | BTH00485US | shower stall | | BTH00415JP | lavatory | +------------+------------------+
The table was used in that chapter to demonstrate how to break
apart the id
values into their
constituent parts and sort them separately, using LEFT()
, MID()
, and RIGHT()
. That led to some fairly
ugly ORDER
BY
clauses, and an issue that I didn’t even
bring up in that chapter was the question of just how to generate the
serial numbers in the middle of the values.
Sometimes you can replace this kind of multiple-part column with
separate columns that are tied together as an AUTO_INCREMENT
index. For example, another
way to manage houseware id
values
like this is to represent them using category
, serial
, and country
columns and tie them together in a
PRIMARY
KEY
with the serial number as an AUTO_INCREMENT
column.
This causes serial numbers to increment independently for each
combination of category and country. To create the table from scratch,
you’d write the CREATE
TABLE
statement like this:
CREATE TABLE housewares ( category VARCHAR(3) NOT NULL, serial INT UNSIGNED NOT NULL AUTO_INCREMENT, country VARCHAR(2) NOT NULL, description VARCHAR(255), PRIMARY KEY (category, country, serial) );
Alternatively, assuming you have the original housewares
table already created in the form
used in the earlier chapter, you can convert it to the new structure
“in place” as follows:
mysql>ALTER TABLE housewares
->ADD category VARCHAR(3) NOT NULL FIRST,
->ADD serial INT UNSIGNED NOT NULL AUTO_INCREMENT AFTER category,
->ADD country VARCHAR(2) NOT NULL AFTER serial,
->ADD PRIMARY KEY (category, country, serial);
mysql>UPDATE housewares SET category = LEFT(id,3);
mysql>UPDATE housewares SET serial = MID(id,4,5);
mysql>UPDATE housewares SET country = RIGHT(id,2);
mysql>ALTER TABLE housewares DROP id;
mysql>SELECT * FROM housewares;
+----------+--------+---------+------------------+ | category | serial | country | description | +----------+--------+---------+------------------+ | DIN | 40672 | US | dining table | | KIT | 372 | UK | garbage disposal | | KIT | 1729 | JP | microwave oven | | BED | 38 | SG | bedside lamp | | BTH | 485 | US | shower stall | | BTH | 415 | JP | lavatory | +----------+--------+---------+------------------+
With the id
values split into
their separate parts, sorting operations become easier to specify
because you can refer to individual columns directly rather than by
pulling out substrings of the original id
column. You can also make sorting more
efficient by adding additional indexes for the serial
and country
columns. But a problem remains: how
to display each product ID as a single string rather than as three
separate values? Do that with CONCAT()
:
mysql>SELECT category, serial, country,
->CONCAT(category,LPAD(serial,5,'0'),country) AS id
->FROM housewares ORDER BY category, country, serial;
+----------+--------+---------+------------+ | category | serial | country | id | +----------+--------+---------+------------+ | BED | 38 | SG | BED00038SG | | BTH | 415 | JP | BTH00415JP | | BTH | 485 | US | BTH00485US | | DIN | 40672 | US | DIN40672US | | KIT | 1729 | JP | KIT01729JP | | KIT | 372 | UK | KIT00372UK | +----------+--------+---------+------------+
You can even eliminate the need for
LPAD()
by
declaring serial
to be a
zero-filled column for which values are displayed using five
digits:
mysql>ALTER TABLE housewares
->MODIFY serial INT(5) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT;
Then MySQL supplies the leading zeros automatically, and
the CONCAT()
expression becomes simpler:
mysql>SELECT category, serial, country,
->CONCAT(category,serial,country) AS id
->FROM housewares ORDER BY category, country, serial;
+----------+--------+---------+------------+ | category | serial | country | id | +----------+--------+---------+------------+ | BED | 00038 | SG | BED00038SG | | BTH | 00415 | JP | BTH00415JP | | BTH | 00485 | US | BTH00485US | | DIN | 40672 | US | DIN40672US | | KIT | 01729 | JP | KIT01729JP | | KIT | 00372 | UK | KIT00372UK | +----------+--------+---------+------------+
This example illustrates an important principle: you might think
about values one way (id
values as
single strings), but that doesn’t mean you must necessarily represent
them in the database that way. If an alternative representation
(separate columns) is more efficient or easier to work with, it may
well be worth using—even if you must reformat the underlying columns
for display purposes to give them the appearance people expect.
If formatting multiple column values into an identifier involves complex calculations or you simply want to hide the details from applications, define a stored function that takes the relevant column values as arguments and returns the identifier. For example:
CREATE FUNCTION houseware_id(category VARCHAR(3), serial INT UNSIGNED, country VARCHAR(2)) RETURNS VARCHAR(10) DETERMINISTIC RETURN CONCAT(category,LPAD(serial,5,'0'),country);
Use the function as follows. The result is the same as before, but the caller need not know how the identifiers are constructed:
mysql>SELECT category, serial, country,
->houseware_id(category,serial,country) AS id
->FROM housewares;
+----------+--------+---------+------------+ | category | serial | country | id | +----------+--------+---------+------------+ | BED | 38 | SG | BED00038SG | | BTH | 415 | JP | BTH00415JP | | BTH | 485 | US | BTH00485US | | DIN | 40672 | US | DIN40672US | | KIT | 1729 | JP | KIT01729JP | | KIT | 372 | UK | KIT00372UK | +----------+--------+---------+------------+
For more information about writing stored functions, see Chapter 16.
18.117.99.71