Other people do know how to read manuals, Paul.
If the original poster made any assumptions about the NEXT value in the auto increment field based on the value of LAST_INSERT_ID, that assumed value will be invalid the second another row is written to the table. If I did this: INSERT INTO foo SET bar='bar'; SELECT LAST_INSERT_ID() -> 1, so next_value = 2 ...someone else writes one row... INSERT INTO foo SET bar='baz'; SELECT LAST_INSERT_ID() -> 3, hey! is not the same as next_value! Same as if I used MAX()... LAST_INSERT_ID is simply the last value YOU inserted. It has no reliable relationship with any values in the table itself, and you can make no valid assumptions about values in the table unless you hold the lock for that table. And you CAN find out what it WILL be (contrary to your statement below), if you lock the table first, and use MAX(). Now, as for whether or not it's USEFUL to know the next value, that's another matter. If what you want is to do something like this: INSERT some row w/o setting the auto_increment column SELECT the auto_increment ID for the row I just wrote Then LAST_INSERT_ID is, indeed, the right way to do that. No locks required. james montebello On Tue, 29 Jan 2002, Paul DuBois wrote: > The original question that started this thread was: > > >> > >>I need to find out what the next value will be in an auto_increment field > >>will be. could someone help me out with the select statement? > > To which I replied: > > > > >You can't find out what it *will be* until you actually create the record. > >Then you get the like this: > > > >SELECT LAST_INSERT_ID(); > > > >Check the discussion for LAST_INSERT_ID() in the MySQL manual. > > > >It will tell you the answer to your next question. :-) > > When I wrote that last sentence, I was anticipating that the original > poster would ask, "but what happens if other clients insert records > and generate new AUTO_INCREMENT values? Won't that cause the value > returned by LAST_INSERT_ID() to become invalid?" > > The answer, of course, is no. LAST_INSERT_ID() is designed specifically > *not* to be affected by activity performed by other clients. It's all > spelled out in the manual. If you don't believe it (and from the amount > of traffic that this thread has generated, apparently many people do not), > here's the reference: > > http://www.mysql.com/doc/M/i/Miscellaneous_functions.html > > > LAST_INSERT_ID() is your friend. Get to know it, it won't let you down. :-) > > --------------------------------------------------------------------- > 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
