MySQL ON DUPLICATE KEY - last insert id?

I have the following query:

INSERT INTO table (a) VALUES (0)
  ON DUPLICATE KEY UPDATE a=1

I want the ID of either the insert or the update. Usually I run a second query in order to get this as I believe insert_id() only returns the 'inserted' ID and not the updated ID.

Is there a way to INSERT/UPDATE and retrieve the ID of the row without running two queries?

Solution 1:

Check this page out: https://web.archive.org/web/20150329004325/https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
At the bottom of the page they explain how you can make LAST_INSERT_ID meaningful for updates by passing an expression to that MySQL function.

From the MySQL documentation example:

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;

Solution 2:

To be exact, if this is the original query:

INSERT INTO table (a) VALUES (0)
 ON DUPLICATE KEY UPDATE a=1

and 'id' is the auto-increment primary key than this would be the working solution:

INSERT INTO table (a) VALUES (0)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), a=1

Is all here: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

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.

Solution 3:

You might look at REPLACE, which is essentially a delete/insert if the record exists. But this would change the auto increment field if present, which could break relationships with other data.

Solution 4:

I don't know what is your version of MySQL but with InnoDB, there was bug with autoinc

bug in 5.1.20 and corrected in 5.1.23 http://bugs.mysql.com/bug.php?id=27405

bug in 5.1.31 and corrected in 5.1.33 http://bugs.mysql.com/bug.php?id=42714

Solution 5:

I have come across a problem, when ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id) increment the primary key by 1. So the id of the next input within the session will be incremented by 2