MySQL Cookbook

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 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 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	2001-10-07	kitchen
millipede	2001-10-07	basement
beetle	2001-10-07	basement
ant	2001-10-07	front yard
ant	2001-10-07	front yard
honeybee	2001-10-08	back yard
cricket	2001-10-08	garage
beetle	2001-10-08	front yard