Octavian, seems like LAST_INSERT_ID() will not always return the correct value. If you use ANSI-SQL INSERT, the function works fine. If you use MySQL extended INSERT (i.e. with more than one record per insert statement), the function will return the ID of the _first_ record inserted with an extended INSERT.
I tried this with MyISAM and InnoDB, and it doesn't seem to be table handler dependent. Below is the output of my test. --- mysql> insert into a values(null, 'one'), (null, 'two'); Query OK, 2 rows affected (0.11 sec) Datensõtze: 2 Duplikate: 0 Warnungen: 0 mysql> select * from a; +----+------+ | id | name | +----+------+ | 1 | one | | 2 | two | +----+------+ 2 rows in set (0.03 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 1 | +------------------+ 1 row in set (0.02 sec) mysql> insert into a values(null, 'three'); Query OK, 1 row affected (0.05 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 3 | +------------------+ 1 row in set (0.00 sec) -- Stefan Hinz <[EMAIL PROTECTED]> Geschäftsführer / CEO iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 ----- Original Message ----- From: "Octavian Rasnita" <[EMAIL PROTECTED]> To: "MySQL" <[EMAIL PROTECTED]> Sent: Tuesday, January 21, 2003 5:52 PM Subject: Which is the difference? > 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) > > #I've tried a second time: > mysql> select last_insert_id() from a; > +------------------+ > | last_insert_id() | > +------------------+ > | 1 | > | 1 | > +------------------+ > 2 rows in set (0.00 sec) > > #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; > +------------------+ > | last_insert_id() | > +------------------+ > | 1 | > +------------------+ > 1 row in set (0.00 sec) > > #I've tried a second time and the value is still "1" > mysql> select last_insert_id() from a limit 1; > +------------------+ > | last_insert_id() | > +------------------+ > | 1 | > +------------------+ > 1 row in set (0.00 sec) > > #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 | > +----+ > | 1 | > +----+ > 1 row in set (0.00 sec) > > #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. > > 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? > > Thank you. > > > > Teddy, > Teddy's Center: http://teddy.fcc.ro/ > Email: [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 > --------------------------------------------------------------------- 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
