MySQL ON DUPLICATE KEY - last insert id?

143
votes

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?

7
mysqlinsert-id
Rather than supposing, why don't you test it yourself? The SQL in the edit above does work, and through my testing is faster than catching an insertion fail, using INSERT IGNORE, or selecting to see if there is a duplicate first. – Michael Fenwick
WARNING: The solution proposed works, but the auto_increment value continues to increment, even if there is no insert. If the duplicate key happens often, you may want to run alter table tablename AUTO_INCREMENT = 0; after the above query, to avoid big gaps in your id values. – Frank Forte

7 Answers

189
votes

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;
38
votes

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.

2
votes

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.

2
votes

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

1
votes

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

0
votes

It's worth noting, and this might be obvious (but I'll say it anyway for clarity here), that REPLACE will blow away the existing matching row before inserting your new data. ON DUPLICATE KEY UPDATE will only update the columns you specify and preserves the row.

From the manual:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

0
votes

The existing solutions work if you use autoincrement. I have a situation where the user can define a prefix and it should restart the sequence at 3000. Because of this varied prefix, I cannot use autoincrement, which makes last_insert_id empty for inserts. I solved it with the following:

INSERT INTO seq_table (prefix, id) VALUES ('$user_prefix', LAST_INSERT_ID(3000)) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id + 1);
SELECT LAST_INSERT_ID();

If the prefix exists, it will increment it and populate last_insert_id. If the prefix does not exist, it will insert the prefix with the value 3000 and populate last_insert_id with 3000.