use Perl

I'm sure this is obvious to a lot of people, but it's a trick that has come in handy for me. Often you want an sequence field that's a bit more clever than just an auto_increment, for example if you want to control the starting point of the sequence (actually you could just insert and then delete junk rows).

The problem I had is that I couldn't just create an auto_increment column as the table had multi-column primary keys and the sequence was based on one of these keys. For example, given this table:

CREATE TABLE widgets (
  line varchar(250) NOT NULL,
  id int(10) unsigned NOT NULL,
  PRIMARY KEY (line, id)
)

With this data:

(foo, 1), (foo, 2), (foo, 3), (bar, 5), (bar, 6)

Each line of widgets has it's own sequence of ids so we need a table to keep track of the next one:

CREATE TABLE next_ids (
  line varchar(250) NOT NULL,
  next_id int(10) unsigned NOT NULL default '1',
  PRIMARY KEY (store)
)

Which then just has one row per sequence:

(foo, 4), (bar, 7)

Then it's just a simple matter of locking the table and grabbing the next id:

LOCK TABLES next_ids WRITE;
SELECT next_id FROM next_ids WHERE line = 'foo';
UPDATE next_ids SET next_id = 5 WHERE line 'foo';
UNLOCK TABLES;

The advantage of using a separate table rather than doing a SELECT MAX(id)... on the widgets table is that we can do things like skip ids or take advantage of Perl's magical ++ to have alphanumeric keys.

I'm sure people who are using "real" databases are laughing at this malarky :)