Yes, you can do this in MySql, given that you are doing it from a procedural
language. You do a "compare and swap," adding 1 to the old value, then do
an SQL update with a where clause that checks the old value. You keep doing
this until you change a row.
Here's some sample code (in SQL and Java/JDBC)
create table XYZ (COUNTER int not null);
insert into XYZ set COUNTER = 0;
Connection con = ...
PreparedStatement doSelect = con.prepareStatement("select COUNTER from
XYZ");
PreparedStatement doUpdate = con.prepareStatement("update XYZ set COUNTER =
? where COUNTER = ?");
...
int newValue;
do {
ResultSet rs = doSelect.executeQuery();
rs.next();
int oldValue = rs.getInt(1);
rs.close();
newValue = oldValue + 1;
doUpdate.setInt(1,newValue);
doUpdate.setInt(2,oldValue);
} while (doUpdate.executeUpdate() == 0);
// -- newValue is the desired sequence number
> Subject: Emulating a sequence in MySQL?
> Date: Fri, 1 Mar 2002 10:37:57 -0500
> From: "Richard Bolen" <[EMAIL PROTECTED]>
> To: "MySQL Mailing List (E-mail)" <[EMAIL PROTECTED]>
>
> 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
