mysql插入一条信息忽略主键_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?

解决方案

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