BMJI, But isn't 'last_insert_id()' only meant to be called after inserting a record?? If you've never inserted a record (using the current connection), it seems like calling 'last_insert_id()' would be a meaningless call. It could return the highest value of the auto-increment column, but it could just as easily return -1 or 0 or Avagadro's number.
I thought it was only meant to retrieve the value that is automatically inserted into an auto-increment column. The column isn't supposed to be set by the INSERT statement, the backend engine will figure out the next available number and put it in. But the client can't know in advance what that value is going to be. So if the client needs to know what was 'decided' by the engine, it can call 'last_insert_id()' AFTER inserting the record. Or am I all wet here... Mike Fochtman > -----Original Message----- > From: Bruce Stewart [mailto:[EMAIL PROTECTED]] > Sent: Friday, February 01, 2002 9:42 AM > To: 'Andreas Schoelver'; [EMAIL PROTECTED] > Subject: RE: AUTO_INCREMENT columns randomly restart counting from 1 > > > Hi Andreas, > > > >I think AUTO_INCREMENT is on a per-connection basis. So if > > you're doing > > >this across different database connections, it will reset to 0. > [snip] > > do a 'select * from <tablename> where test=1' and you will > > see that the 2 new > > values will be visible to both of the clients. > > As I understand it, this is correct, and by design. MSSQL > functions in the > same way (@@IDENTITY). Selecting "last_insert_id()" returns > the last insert > id for YOUR client connection, and no-one elses. You should > normally call > last_insert_id() to find out what value MySQL assigned to the > autoinc column > in your newly inserted record, so that you can use that > "last_insert_id()" > value when inserting other related data into other tables, where the > relationship is based on that ID field. > > > the only special thing to recognize is with function > last_insert_id(). > > if one client creates a new autoincrement value, the other > > client can't see this, > > the result for 'select last_insert_id()' is 0! > > only the client performing the increment is able to determine > > the new value valid > > for _this_ client. > > imagine that both clients did their increments on the same > > table, last_insert_id() gives > > different answers to both of them. > > on the other hand: 'select max(id)' gives the absolute > > maximum to both of the clients. > > > > Correct, and it should! > > Don't know if I've helped you here, or only confused you > more, but this > functionality is by design, and I believe makes sense. > > Cheers, > Bruce > > > --------------------------------------------------------------------- > 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
