List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 13 2001 10:57pm
Subject:Re: Rolling over on sequence number?
View as plain text  
At 10:06 AM +0100 9/13/01, Robin Keech wrote:
>I have a requirement to use a sequence number table that I can reserve a
>block of sequences, (so auto increment is not suitable).
>
>This is my proposed table structure
>
>create table SEQNO (
>    SEQNO INT UNSIGNED NOT NULL DEFAULT 0,
>    SESSION VARCHAR(10),
>    LOCK_TIMEOUT TIMESTAMP);
>
>my blocks will go up in 100's, using SQL like
>
>update SEQNO set SEQNO = SEQNO + 100;
>
>My question is, is there any way to get the database to roll over the int
>value when it gets to 4,294,967,295?
>
>In my tests it goes upto the max value and stays there.  I could do it
>programmatically, but it would be so much nicer if the database rolled over
>the value.
>
>for example
>
>4,294,967,200
>+         100
>-------------
>             4

Why 4?  You want the value mod 4,294,967,296?

Write your update like this:

update SEQNO set SEQNO = MOD(SEQNO + 100, 4294967296)

>
>
>Any ideas?
>
>I have read manual, and MySQL book regarding sequences, but could not find
>anything relevant.
>
>Thanks
>
>Robin


-- 
Paul DuBois, paul@stripped
Thread
Rolling over on sequence number?Robin Keech13 Sep
  • Re: Rolling over on sequence number?Paul DuBois14 Sep