At 1:49 PM +0200 6/26/01, Gunnar von Boehn wrote:
>Hello everybody,
>
>
>I thing, that I have a problem that needs a sequence and that
>auto-increment can't do the job this time.
>
>I already consulted the manual and the mailarchive.
>I saw several different proposals, now I'm a bit confused and don't
>know exactly what the rigth way is to do it.
>
>Could somebody please clarify the following?
>
>
>We are using ISAM tables on mysql 3.22.32.
I would use MyISAM, not ISAM, but the instructions below should work
regardless.
>
>We have an id-column but the id will be generated out of two different ranges.
>Example: all VIP-customers will get an id from 1000-9999 and
>normal customers from 10000-9999999.
>
>So, I think the only way to solve this is a sequence table.
>
>Lets take a single row, integer value for holding the sequence:
>CREATE TABLE sequence(
> id int4;
>);
If you're going to need two sequences, you need two sequence generators.
A single table with a single column won't do it. Try this, which will
work for any number of sequences. First, create a table to hold your
sequence numbers:
# Create a table to be used for multiple sequences
CREATE TABLE sequence
(
name CHAR(20) NOT NULL PRIMARY KEY,
seq BIGINT UNSIGNED NOT NULL
);
name identifies your sequence. It's a PRIMARY KEY for fast lookups.
seq is the current sequence value for a given sequence.
Next, intialize your sequence counters to one less than the first number
you want to use in each sequence:
INSERT INTO multiseq (name, seq) VALUES("vip",999);
INSERT INTO multiseq (name, seq) VALUES("normal",9999);
To generate a new VIP sequence number, do this:
UPDATE multiseq SET seq = LAST_INSERT_ID(seq+1) WHERE name = "vip";
SELECT "vip", LAST_INSERT_ID();
To generate a new Normal sequence number, do this:
UPDATE multiseq SET seq = LAST_INSERT_ID(seq+1) WHERE name = "normal";
SELECT "normal", LAST_INSERT_ID();
LAST_INSERT_ID() with an argument creates a value that can be treated
just like it's an AUTO_INCREMENT value. That means you can call
LAST_INSERT_ID() without an argument later in the current session to
retrieve the value you generated. This also has the advantage that you
don't need to set up any locks.
The table can be extended to provide more than two sequences simply by
inserting new rows. (This is one way to main hit counters for a bunch of
Web pages using a single table, for example - although you'd want to use
a longer name column in that case...)
>
>
>My question is this:
>Is this the best way to get and evalute the sequence?
>
> LOCK TABLES sequence WRITE;
> UPDATE sequence SET id=id+1;
> SELECT id FROM sequence;
> UNLOCK TABLES;
>
>
>
>Thanks
>Gunnar von Boehn
--
Paul DuBois, [EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
