nextval in mysql?

[mrdance]

OK, let's say I'm building a forum. I have on unique autoincrement id for each message of the forum. I have also on threadid which cannot be a autoincrement. I have to update that one manually. I have to get the latest value of that one. In Oracle I create a sequence and use nextval. I "know" that I will get a unique then. I know I can use "select max(threadid)" in mysql but it "can" result in two people requesting and getting the same threadid. It's a very small risk but I don't want to live with that risk. Isn't there any function for this?

Thanks / Henrik

 

[rycamor]

Those are two different problems. You can easily constrain a column to UNIQUE, without specifying autoincrement. At this point, then it is up to your application to keep track of id's and create new ids. Fortunately MySQL lets you fetch the last insert ID (in PHP it is mysql_insert_id(resource)).

So all you need is to check the last insert ID, then generate a new value, and attempt to insert a new row with that id value. In the unlikely even that someone else has grabbed the same insert ID, MySQL will enforce uniqueness, so "first come, first served". Then the script just need an error/fallback, which generates another ID in the sequence and attempts another insert, upon failure. Obviously, you also need to be careful not to allow an endless loop.

 

[mrdance]

I try to understand it but it looks like mysql_insert_id only works on a column with aútoincrement?? Am I wrong?

Thanks Henrik

 

[mrdance]

what about this sql? It doesn't work but I would like it to do? If this work, does it take a lot of system resources?

INSERT INTO test (thread) VALUES ((select max(thread)+1 from test))

Remember that I can't use unique on thread, cause that one can have several records with the same thread. It's when a new thread is created I would like the above to work. Do I eliminate risks of two writing the same "new" thread?

Thanks / Henrik