mysqli::insert_id returning wrong id on duplicate key
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 increm开发者_高级运维ent 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?
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.
精彩评论