mysql - after insert ignore get primary key

Question

i am running a query in mysql insert ignore into........ using Python

after running the query I want to know the primary key of the row. I know there is the query

SELECT LAST_INSERT_ID();

but i'm not sure if it will work with insert ignore

what is the best way to do this?

Unfortunately, it will not work, despite official documentation to the contrary; INSERT IGNORE followed by LAST_INSERT_ID() will return the last inserted ID (or 0 if there wasn't an insert in the same session). See example: pastebin.com/KQaCQABR — fatal_error

Piskvor left the building Piskvor left the building · Accepted Answer · 2011-06-09T10:44:32

The documentation for LAST_INSERT_ID() says:

If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT counter is not incremented and LAST_INSERT_ID() returns 0, which reflects that no row was inserted.

Knowing this, you can make this a multi-step process:

INSERT IGNORE
if LAST_INSERT_ID(), then done (new row was inserted)
else SELECT your_primary key FROM yourtable WHERE (your inserted data's UNIQUE constraints)

Example with U.S. states:

id  | abbrev | other_data
 1  | AL     | ...
 2  | AK     |

UNIQUE KEY abbr (abbrev)

Now, inserting a new row:

INSERT IGNORE INTO `states` (`abbrev`,`other_data`) VALUES ('AZ','foo bar');
> OK
SELECT LAST_INSERT_ID();
> "3"
// we have the ID, we're done

Inserting a row which will be ignored:

INSERT IGNORE INTO `states` (`abbrev`,`other_data`) VALUES ('AK','duplicate!');
> OK
SELECT LAST_INSERT_ID();
> "0"
// oops, it already exists!
SELECT id FROM `states` WHERE `abbrev` = 'AK'; // our UNIQUE constraint here
> "2"
// there we go!

Alternately, there is a possible workaround to do this in one step - use REPLACE INTO instead of INSERT IGNORE INTO - the syntax is very similar. Note however that there are side effects with this approach - these may or may not be important to you:

REPLACE deletes+recreates the row
- so DELETE triggers are, um, triggered
- also, the primary ID will be incremented even if the row exists
- INSERT IGNORE keeps the old row data, REPLACE replaces it with new row data

mysql - after insert ignore get primary key

2 Answers