Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

gav (2710)

gav
  (email not shown publicly)
http://www.estey.com/
AOL IM: flufflegavin (Add Buddy, Send Message)

Hacker in NYC.

Journal of gav (2710)

Monday July 07, 2003
04:52 PM

MySQL sequences

[ #13311 ]

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

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • 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 [mysql.com]. It's only for MyISAM (which I use) and BDB (which is dead). Maybe you're using InnoDB.
    • 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.