开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜