Yep. Check out the last_insert_id function that takes an argument: create table sequence (id int not null); insert into sequence values (0); update sequence set id=last_insert_id(id+1); select last_insert_id();
Repeat the last 2 lines a few times and you'll see that last_insert_id() returns the last sequence you created. It's also connection specific -- so if another connection updates the sequence table after your connection, but before you use last_insert_id() again, you're still okay. Each connection maintains it's own last_insert_id reference. See the doc, about halfway down the page: http://www.mysql.com/doc/M/i/Miscellaneous_functions.html --jeff ----- Original Message ----- From: "Richard Bolen" <[EMAIL PROTECTED]> To: "MySQL Mailing List (E-mail)" <[EMAIL PROTECTED]> Sent: Friday, March 01, 2002 7:37 AM Subject: Emulating a sequence in MySQL? > I need to have unique id's for every data element in my system no matter which table it's in. In Oracle I can create a sequence and with one SQL query I can increment the value and retrieve it for use in my next insert. > > Can I do this in MySQL? I know about AUTO INCREMENT but that appears to only work on a per table basis. Another key requirement is being able to increment the value and retrieve it with one SQL call. > > I'm thinking that I can create a table with one column to represent my sequence. The question I have is can I increment the value and retrieve it with one SQL statement? > > This may sound like a strange set of requirements but we're trying to get our app (a Java JDBC thing) to work across Oracle and MySQL without code changes. > > Thanks, > Rich > > -------------------------------------------------------------------- > Rich Bolen > Senior Software Developer > GretagMacbeth Advanced Technologies Center > 79 T. W. Alexander Drive - Bldg. 4401 - Suite 250 > PO Box 14026 > Research Triangle Park, North Carolina 27709-4026 USA > Phone: 919-549-7575 x239, Fax: 919-549-0421 > > http://www.gretagmacbeth.com/ > -------------------------------------------------------------------- > > > --------------------------------------------------------------------- > 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
