Recipe 11.11. Using an AUTO_INCREMENT Column to Create Multiple Sequences

Problem

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.

Solution

Link the AUTO_INCREMENT column to those other columns, making them all part of the same index.

Discussion

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 completedexcept 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 randombut 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 Section 7.10, 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 usingeven 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.