Hello. On Tue 2003-01-21 at 18:52:06 +0200, [EMAIL PROTECTED] wrote: > Hello all, > > I've tried the following sql queries: > > mysql> create table a(id int unsigned not null auto_increment primary key, > name text); > Query OK, 0 rows affected (0.01 sec) > > mysql> insert into a values(null, 'one'), (null, 'two'); > Query OK, 2 rows affected (0.01 sec) > Records: 2 Duplicates: 0 Warnings: 0 > > mysql> select last_insert_id() from a; > +------------------+ > | last_insert_id() | > +------------------+ > | 1 | > | 1 | > +------------------+ > 2 rows in set (0.01 sec)
Well, that doesn't really make sense, because LAST_INSERT_ID() is not bound to rows like this. Just use SELECT LAST_INSERT_ID(); without any reference to a table. What you did is like SELECT SIN(5) FROM a; i.e. it will simply return the constant value for each row found. > #I've tried a second time: > mysql> select last_insert_id() from a; The result of LAST_INSERT_ID() does not change from selects (except for the ODBC case below). > #I've tried to put a limit clause to see the last inserted ID only once: > mysql> select last_insert_id() from a limit 1; That's the wrong way to query LAST_INSERT_ID(). See above. > #Now I've tried to find the last inserted ID by using "where id is null" but > ... > mysql> select id from a where id is null; "id IS NULL" is supported for compatibility with ODBC. Don't use it in your programs, except if you have to. > #The first trial was successfully, but the second not: > mysql> select id from a where id is null; > Empty set (0.01 sec) > > #And from this point on, I get only empty responses. > Please tell me why. I presume that is the behaviour ODBC expects. Since it's mainly for ODBC compatibility, don't wonder about it. It would have been made to return random results if that was what ODBC expected. So your question should rather be: why does ODBC expect it this way. I don't know (and I don't care). > And BTW, if I insert more records in a single query, how can I find > the real last one? > Is the only solution counting the number of new entered records, and > adding this number to the number returned by the last_insert_id() > function? Yes. As http://www.mysql.com/doc/en/Miscellaneous_functions.html explains, LAST_INSERT_ID() returns the first inserted row to make it easier to reproduce the same insert. HTH, Benjamin. -- [EMAIL PROTECTED] --------------------------------------------------------------------- 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
