Hi Sven, Last_insert_id is not what your're looking for, for the same reason you give about max(id) : your insert can find a duplicate key because another user inserted an id between two of yours.
Last_insert_id gives just the last auto_increment id for success in insertion. Suppose that your insert fails, you try update, but if the update fails, last_insert_id=0. Since you have a UNIQUE index on 'value', i don't understand why you use auto_increment ? Also, since it's unique : select id where value = 'what you inserted' gives you the row you inserted, or the row that was updated. it seems to me that this is a "false problem" as it's sais in french. you can see php mysql_last_id, mysql_afected_rows ... if you use php. Mathias Selon Sven Paulus <[EMAIL PROTECTED]>: > On 19.05., [EMAIL PROTECTED] wrote: > > 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. > > Yes, if I insert an new value then the ID column gets incremented. But if I > try to insert an existing value (in the second column), the LAST_INSERT_ID() > contains a random value (the next auto_increment value going to be used?) > afterwards. > So I can't rely in retrieving LAST_INSERT_ID(). > > > Since last_insert_id() has a connection scope, it's better for you to use : > > select max(id) from bla1; > > That's not what I want. I need the ID of the row just inserted (for > referencing it from another table). > > If I select the max(id), then one the hand someone might have added another > row in the meantime and on the other hand is still don't get the ID value in > the ON DUPLICATE KEY UPDATE case ... > > Sven > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
