Using an AUTO_INCREMENT Column to Create Multiple Sequences

11.14.1 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.

11.14.2 Solution

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

11.14.3 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 record, regardless of the contents of the rest of the record. As of MySQL 3.23.5, it's possible for MyISAM tables 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:

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), 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 PRIMARY KEY (name, id), date DATE NOT NULL, # date collected origin VARCHAR(30) NOT NULL # where collected );

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 | 2001-10-07 | kitchen | | 1 | millipede | 2001-10-07 | basement | | 1 | beetle | 2001-10-07 | basement | | 2 | ant | 2001-10-07 | front yard | | 3 | ant | 2001-10-07 | front yard | | 1 | honeybee | 2001-10-08 | back yard | | 1 | cricket | 2001-10-08 | garage | | 2 | beetle | 2001-10-08 | front yard | | 1 | termite | 2001-10-09 | kitchen woodwork | | 2 | cricket | 2001-10-10 | basement | | 2 | termite | 2001-10-11 | bathroom woodwork | | 2 | honeybee | 2001-10-11 | garden | | 3 | cricket | 2001-10-11 | garden | | 4 | ant | 2001-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'll be more 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 | 2001-10-07 | kitchen | | 2 | ant | 2001-10-07 | front yard | | 3 | ant | 2001-10-07 | front yard | | 4 | ant | 2001-10-11 | garden | | 1 | beetle | 2001-10-07 | basement | | 2 | beetle | 2001-10-08 | front yard | | 1 | cricket | 2001-10-08 | garage | | 2 | cricket | 2001-10-10 | basement | | 3 | cricket | 2001-10-11 | garden | | 1 | honeybee | 2001-10-08 | back yard | | 2 | honeybee | 2001-10-11 | garden | | 1 | millipede | 2001-10-07 | basement | | 1 | termite | 2001-10-09 | kitchen woodwork | | 2 | termite | 2001-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 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. Suppose you're recording subject information for a research project in which you have control and experimental conditions that you administer to male and female subjects. To assign a separate set of sequence numbers for each sex in each of the two conditions, create a three-column AUTO_INCREMENT index:

CREATE TABLE subj_list ( name CHAR(40), # subject name condition ENUM('control','experimental') NOT NULL, sex ENUM('M','F') NOT NULL, id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (condition, sex, id) );

MySQL's mechanism for multiple-column sequences can be easier to use than logically equivalent single-column values. Recall that in Recipe 6.13, 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( ). This 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 the serial numbers in the middle of the values are to be generated.

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 would cause 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? That can be done 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 will supply 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 alternate 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.