At 17:38 -0800 1/29/02, James Montebello wrote: >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.
Which is why I said that you can't find out the value until you create it. Trying to get it ahead of time is a waste of time. And as you point out below, whether that is even useful is open to debate. > >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. I don't think that I claimed otherwise. > 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(). Actually ... That's not true, unless you can guarantee that the next AUTO_INCREMENT value will be the maximum value currently in the table + 1. Which is not, in general, true. It will be true for ISAM tables, but can easily be false for MyISAM tables, for example. (Specifically, it's true for table types that reuse deleted values at the top of the sequence. For table types that guarantee a strictly monotonic sequence, MAX() bears no relationship to the next AUTO_INCREMENT value.) > >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. Right. My (apparently quite evident) despair at the course of this thread is not over the finer points of how LAST_INSERT_ID() behaves, but over the extent to which its basic essential nature seems to go unnoticed. That's too bad, because it saves many headaches and lots of messing around that would otherwise be necessary. --------------------------------------------------------------------- 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
