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
