Autoincrement is sometimes called a “poor-man-sequence”. Sequences in other DB systems are counters that can be used for automatically number fields when inserting data, just like autoincrement in MySQL does, but they can be much more complex. However, in MySQL you do not always you want or need increments of 1. Sometimes you need something a little more complex than that and MySQL leaves you pretty much in the cold.  There is a neat little trick that can solve some “autoincrement” problems…

The “trick”

It is not so much a trick as it is a poorly documented feature. Suppose you need data like this:

item        subitem
    1            1
    1            2
    1            3
    2            1
    2            2
    3            1

You want to autoincrement the subitem field, but as soon as you insert an new item, it has to start again with 1. What you can do is create a composite primary key from the item and subitem fields. Then you add the autoincrement value to the subitem field. This will automatically number your subitems correctly.

CREATE TABLE IF NOT EXISTS `test` (
  `item` int(11) unsigned NOT NULL,
  `subitem` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(100) character set ascii NOT NULL,
  PRIMARY KEY  (`item`,`subitem`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The major drawback though is that it can only be used with MyISAM tables. InnoDB does not support this kind of autoincrements so you have to rely on triggers / Stored procedures again.