All the Perl that's Practical to Extract and Report

MySQL sequences

gav on 2003-07-07T21:52:10

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 :)

Unnecessarily complicated?

vsergu on 2003-07-07T22:05:10

I haven't used it, but MySQL auto_increment columns are supposed to work that way already, aren't they? Define a multicolumn primary key where the last part is an auto_increment, and it works.

Oh, I see. It's only for MyISAM (which I use) and BDB (which is dead). Maybe you're using InnoDB.

Re:Unnecessarily complicated?

gav on 2003-07-07T22:23:17

You are right, I should have mentioned that.

The real reason for doing it the complicated way was that I needed it for order ids and I needed to be able to finer control over sequences.

I've also used it to port a MS-SQL database where products had positive ids and sub-products had negative ids and I needed to have both in the same table.

Re:Unnecessarily complicated?

vsergu on 2003-07-07T22:37:10

... where products had positive ids and sub-products had negative ids

Ick.