Hi, If you add another command, mysql> insert into bla1 values (NULL, "Cello3", NULL) on duplicate key update whentime = NOW();
The right ID will be used. Since last_insert_id() has a connection scope, it's better for you to use : select max(id) from bla1; Mathias Selon Sven Paulus <[EMAIL PROTECTED]>: > Hi, > > I'd like to insert string values into a table. If I add a new string, I want > to get back the value of the AUTO_INCREMENT column. If the string already > exists in the table, I'd like to get the AUTO_INCREMENT value of the existing > entry. > > I thought this might be possible using INSERT ... ON DUPLICATE KEY UPDATE > ..., but LAST_INSERT_ID() seems to be unusable in this case. > > Example: > > mysql> CREATE TABLE `bla1` ( > -> `id` int(10) unsigned NOT NULL auto_increment, > -> `value` varchar(255) default NULL, > -> `whentime` timestamp(14) NOT NULL, > -> PRIMARY KEY (`id`), > -> UNIQUE KEY `value` (`value`) > -> ) TYPE=MyISAM > -> ; > Query OK, 0 rows affected, 1 warning (0.10 sec) > > mysql> insert into bla1 values (NULL, "Cello", NULL) on duplicate key update > whentime = NOW(); > Query OK, 1 row affected (0.08 sec) > > mysql> select LAST_INSERT_ID(); > +------------------+ > | LAST_INSERT_ID() | > +------------------+ > | 1 | > +------------------+ > 1 row in set (0.04 sec) > > mysql> insert into bla1 values (NULL, "Hallo", NULL) on duplicate key update > whentime = NOW(); > Query OK, 1 row affected (0.01 sec) > > mysql> select LAST_INSERT_ID(); > +------------------+ > | LAST_INSERT_ID() | > +------------------+ > | 2 | > +------------------+ > 1 row in set (0.00 sec) > > mysql> select * from bla1; > +----+-------+---------------------+ > | id | value | whentime | > +----+-------+---------------------+ > | 1 | Cello | 2005-05-18 17:14:53 | > | 2 | Hallo | 2005-05-18 17:15:38 | > +----+-------+---------------------+ > 2 rows in set (0.00 sec) > > mysql> insert into bla1 values (NULL, "Cello", NULL) on duplicate key update > whentime = NOW(); > Query OK, 2 rows affected (0.00 sec) > > mysql> select LAST_INSERT_ID(); > +------------------+ > | LAST_INSERT_ID() | > +------------------+ > | 3 | > +------------------+ > 1 row in set (0.00 sec) > > mysql> select * from bla1; > +----+-------+---------------------+ > | id | value | whentime | > +----+-------+---------------------+ > | 1 | Cello | 2005-05-18 17:15:58 | > | 2 | Hallo | 2005-05-18 17:15:38 | > +----+-------+---------------------+ > 2 rows in set (0.00 sec) > > > I expected that the last INSERT clause would set the LAST_INSERT_ID() to 1. > But of course the MySQL manual says "The value of LAST_INSERT_ID() is not > changed if you update the AUTO_INCREMENT column of a row with a non-magic > value (that is, a value that is not NULL and not 0)." Ok, I didn't even > update the id column at all. If I use id=NULL in the UPDATE clause, the id > column is changed to 0 and the LAST_INSERT_ID() still contains the wrong > value ... > > Now I'm curious if it's possible at all to use LAST_INSERT_ID() together > with INSERT ... ON DUPLICATE KEY UPDATE ... - how can I determine if a > row was inserted or updated and if I can trust the LAST_INSERT_ID() > value? > > Something like > SET @myid:=0 > ... ON DUPLICATE KEY UPDATE @myid:=id > doesn't work, since I can't assign user variables in this part. > > So, is there a way to INSERT an entry if neccessary and always get the id of > the entry? > > Sven > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
