Mysql – thesql – after insert ignore get primary key

MySQL

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?

Best Answer

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
Related Topic