Hi all,
Sorry to jump in the middle of this thread but there is a much simpler
way to generate sequence numbers:
You create an auxiliary table with a one row:
CREATE TABLE sequence (
code int(11) DEFAULT '0' NOT NULL
);
INSERT INTO sequence VALUES (0);
And, each time you need a new sequence number:
UPDATE sequence SET code=LAST_INSERT_ID(code+1);
SELECT LAST_INSERT_ID();
We use this method in our applications and it works well.
(and I didn't invent it, it is described in Paul DuBois'book ;))
Hope this helps
--
Joseph Bueno
Mike Hillyer wrote:
> I was thinking of a similar problem as yours (having the select max(id) type
> query get duplicate results) and what I realized was that I could do a
> select max(id) and specify a where clause to a column that had the user id
> of the creator of the row (which I already had in place for auditing) and
> then there was never duplication because the odds of you yourself inserting
> two rows simultaneously was nil. So, that may work for you as long as each
> user is logged in separately, and you can accept putting a field in your row
> referring to the row creator.
>
> Mike Hillyer
>
> -----Original Message-----
> From: Chuck Tomasi [mailto:Chuck.Tomasi@;plexus.com]
> Sent: Monday, October 21, 2002 7:19 AM
> To: [EMAIL PROTECTED]
> Subject: RE: Using AUTO_INCREMENT like SEQUENCE - Resolved
>
>
> This past weekend the solution came to me. Simple, but elegant (well not as
> elegant as using the auto_increment feature natively, but a lot less code to
> write in my case.)
>
> I'm already used to managing a separate sequence so I too the AUTO_INCREMENT
> feature out of the main table (ex: mytable) and created a second table
>
> Create table mytable_seq (ID int not null primary key auto_increment);
>
> To 'seed' the table I put in the highest value from mytable (ex: select
> max(ID) from mytable). Now when I do this:
>
> Insert into mytable_seq values (NULL);
> Select LAST_INSERT_ID() from mytable_seq limit 1;
>
> I get what I want, what I need to create the file, populate mytable, etc.
> LAST_INSERT_ID() takes care of managing contentions (which I tested to no
> end.) That's the basics of my GetCounter($table) routine!
>
> My only gripe now is that mytable_seq continues to grow and grow (hence the
> need for LIMIT 1). I know it isn't a large table, but I may consider
> trimming it from time to time once I've verified its usefulness.
>
> --Chuck
>
> -----Original Message-----
> From: Peter Brawley [mailto:peter.brawley@;artfulsoftware.com]
> Sent: Friday, October 18, 2002 11:39 AM
> To: Chuck Tomasi; [EMAIL PROTECTED]
> Subject: Re: Using AUTO_INCREMENT like SEQUENCE
>
> Chuck,
>
> last_insert_id() is per-connection. How do you see two users getting the
> same auto_increment value?
>
> PB
>
> -----
> ----- Original Message -----
> From: "Chuck Tomasi" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Friday, October 18, 2002 9:07 AM
> Subject: Using AUTO_INCREMENT like SEQUENCE
>
>
>
>>General Info:
>>
>>OS: Mac OS X
>>MySQL version: 3.23.52 (Jaguar)
>>PERL: 5.6.0
>>DBI: 1.30
>>
>>I've got an application that was written to use Oracle sequences, as such
>>the program pulls a sequence number then creates a file based on that
>>number and populates a database record with infomation. I've read
>>everything I can find out AUTO_INCREMENT values, but I can't seem to
>>reproduce the same functionality without causing some possible conflict
>>condition where two people may get the same numbers.
>>
>>At the heart of the matter is a Perl sub I call "GetCounter()". It
>>accepts a sequence name, pulls a value from the sequence and returns that
>>value. Originally I was doing this with mSQL sequences. With a minor
>>change to the code it ran fun in Oracle also. Now I'm looking at porting
>>my stuff to MySQL, but can't seem to match the functionality.
>>
>>Note, I'm importing a lot of these records from the Oracle DB to MySQL so
>>the counter would not be starting at 0.
>>
>>Thoughts so far:
>>
>>I've thought about "select MAX(ID+1) from mytable", but if two people
>>happen to be doing this at the same time, they'll get the same number -
>>bad.
>>
>>"select LAST_INSERT_ID(ID+1)" seems like a similar solution since two
>>people could get the same LAST_INSERT_ID().
>>
>>I'd like some way to pull the number and make sure it is not pulled again.
>>By the time I need the number, the information has been verified. The
>>record will be populated using this number (ID field), a file will be
>>created using this number, etc. I'd hate to have to do something like
>>
>>"insert into mytable (ID) values (NULL)"
>>
>>only to go back and
>>
>>"select LAST_INSERT_ID(ID) from mytable"
>>"update mytable set LastName='blah',FirstName='foo',Phone='xxx',... where
>>ID=LAST_INSERT_ID()"
>>
>>--Chuck
>>
>>
>>---------------------------------------------------------------------
>>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
>>
>>
>>
>
>
>
> ---------------------------------------------------------------------
> 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
>
>
> ---------------------------------------------------------------------
> 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
>
>
---------------------------------------------------------------------
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
