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?

Answer 1

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

Answer 2

Try using ON DUPLICATE KEY instead of INSERT IGNORE, maybe this can work for you:

INSERT INTO your_table (`id`,`val`) VALUES(1,'Foo') ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(`id`);  SELECT LAST_INSERT_ID(); 

Also see related question: MySQL ON DUPLICATE KEY - last insert id?