MySQL ON DUPLICATE KEY - last insert id?

Question

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?

Dinnerware

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. – Hobard

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. – Til

@FrankForte You are joking about alter table at run time, with concurrent users, in production, right? – Latoria

Answer 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;

Unburden

Somehow I missed that when looking at that page. So the update portion appears as : UPDATE id=LAST_INSERT_ID(id) And that works great. Thanks! – Dinnerware

It is said that php function mysql_insert_id() returns correct value in both cases: php.net/manual/en/function.mysql-insert-id.php#59718. – Kaufman

What in seven hells means that the value is "not meaningful"?? :-| – Posting

@PetrPeller - well, without looking at the MySQL internals, it probably means that it will produce a value, but that value is not related to the query you just ran. In other words, a problem that is a pain to debug. – Injustice

After 5.1.12 this is supposedly no longer necessary, however I found an exception to that today. If you have an autoincrement pk, and a unique key on say an email address, and the 'on duplicate update' triggers based on the email address, note that the last_insert_id' will NOT be the autoincrement value of the updated row. It appears to be the most recently inserted autoincrement value. This makes a huge difference. The work around is the same as shown here, namely to use id=LAST_INSERT_ID(id) in the updating query. – Punnet

On 5.5 @sckd's comment still holds true. – Coelenterate

Now the quoted part has changed, for the Docs: If a table contains an AUTO_INCREMENT column and INSERT ... ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. – Wainscot

will this work for pdo and php? $pdo->lastInsertId(); – Decorticate

@ErenorPaz Of course, LAST_INSERT_ID() always returns the AUTO_INCREMENT value. But which one? What does it do before and after insertion or update? – Oud

Answer 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.

Resist

Yes, see the accepted answer for the same what you said. No need to revive 3 year old posts. Thanks for your effort anyway. – Epner

@tombom the only reason why i posted this answer is because the accepted answer is not correct - it won't work if there is nothing to update. – Resist

Answer 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.

Musa

Ah yeah - I'm looking for something that won't get rid of previous ID's – Dinnerware

This may be dangerous also because this may also cause deletion of another related data (by constraints). – Resolved

Answer 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

Repertory

Answer 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

Berar

Answer 6

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.

Mweru

Answer 7

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.

Poseur