On Tuesday 26 June 2001 04:49, Gunnar von Boehn wrote:
> Hello everybody,
>
>
> I thing, that I have a problem that needs a sequence and that
> auto-increment can't do the job this time.
>
> So, I think the only way to solve this is a sequence table.
>
> Lets take a single row, integer value for holding the sequence:
> CREATE TABLE sequence(
> id int4;
> );
>
>
> My question is this:
> Is this the best way to get and evalute the sequence?
>
> LOCK TABLES sequence WRITE;
> UPDATE sequence SET id=id+1;
> SELECT id FROM sequence;
> UNLOCK TABLES;
This is more work than you need. You can use this sequence:
UPDATE sequence SET id=LAST_INSERT_ID(id+1)
SELECT LAST_INSERT_ID()
The first statement updates the sequence table atomically. It sets the id
value to the id+1 and sets the last insert ID to id+1. This is done
atomically. The next statement gets back the ID that was set in the first
one. Notice that the SELECT does not have a FROM clause! The database
handles the value of LAST_INSERT_ID on a connection by connection basis so,
you'll get the right value.
This set of SQL statements does not require table locks. We use it all the
time.
Best,
Kyle
---------------------------------------------------------------------
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
