mysqli::insert_id returning wrong id on duplicate key

Question

When using mysqli::insert_id in PHP for INSERT INTO ON DUPLICATE KEY UPDATE, I keep getting the next auto increment rather than the updated row if the row is updated. In the same database but in another table, I don't have this behavior when using ON DUPLICATE KEY UPDATE. Instead, I get the id of the updated row. But now for some reason in a new table I have created, I keep getting the next id that doesn't even exist. Both tables are MyISAM, and have an auto increment field. I don't understand why they behave differently.

Example:

==================================
Table: example
==================================
id  |   unique_field    |   data
==================================
1   |   unique1         |   123
2   |   unique2         |   456

INSERT INTO
    example
SET
    unique_field = 'unique1',
    data = '321'
ON DUPLICATE KEY UPDATE
    data = '321'


// mysqli::insert_id returns 3 (not 1)!!

Any ideas?

Wesley van Opdorp Wesley van Opdorp · Accepted Answer · 2011-09-14T13:54:19

From the mysql manual:

If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3; The DELAYED option is ignored when you use ON DUPLICATE KEY UPDATE.

Hope that helps.

mysqli::insert_id returning wrong id on duplicate key

1 Answers