28

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?

John
  • 21,047
  • 43
  • 114
  • 155
  • 2
    Try it. It should work. Let us know if it doesnt – Jai Jun 09 '11 at 10:37
  • See this: http://stackoverflow.com/questions/778534/mysql-on-duplicate-key-last-insert-id – Timo Huovinen Jul 30 '13 at 11:27
  • 1
    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 Feb 05 '15 at 15:57

3 Answers3

42

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
Piskvor left the building
  • 91,498
  • 46
  • 177
  • 222
  • 11
    WARNING: If there was a similar insert just prior which DID have a last_insert_id(), then that value will be returned instead which will break your algorithm. In other words, if you `insert ignore` and an actual insert occurs (say value 3), and then `insert ignore` again with no change, last_insert_id() will return 3. – fatal_error Feb 03 '15 at 22:20
  • 1
    @JamiesonBecker: That is in direct contradiction with what documentation says (and what I see this code doing). In your example, `LAST_INSERT_ID()` will return `0`, as per documentation. The only relevant part I can find is `If the previous statement returned an error, the value of LAST_INSERT_ID() is undefined. For transactional tables, if the statement is rolled back due to an error, the value of LAST_INSERT_ID() is left undefined.` which is very different from what you're claiming. Please provide evidence for your claim. – Piskvor left the building Feb 04 '15 at 12:40
  • Sorry, @Piskvor, `undefined` means not defined, not 0, but you are right that that's what the documentation says. The documentation is unambiguous but clearly wrong, at least for MySQL 5.5.41. last inserted ID's are simply not cleared or reset to 0 upon an ignored insert, but the previous value is returned. Evidence, try it yourself: pastebin.com/KQaCQABR – fatal_error Feb 05 '15 at 15:54
  • I'm aware that "undefined" means "anything could be there, including Aunt Matilda" ;) What you pasted looks like a bug. – Piskvor left the building Feb 06 '15 at 15:03
  • A bug in the server or in the docs? I'd tend to agree with both :) – fatal_error Feb 06 '15 at 16:22
  • Server, I would say. One more thing to look out for :( – Piskvor left the building Feb 06 '15 at 16:25
20

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?

Community
  • 1
  • 1
Timo Huovinen
  • 53,325
  • 33
  • 152
  • 143
0

I don't think the preferred answer is correct, at least not in the mysql versions I tried (5.7.12 and 8.0.33) and for InnoDB tables. Only @fatal_error states the real behaviour. LAST_INSERT_ID() returns the id of the last successful insert, irrespective of any INSERT IGNORE which did not result in an actual record creation in the same database connection (enclosing in a TRANSACTION does not change behaviour). This seems consistent with the documentation. Also, the id is incremented even if the INSERT IGNORE does not create a record, so that at the next successful INSERT the id will be incremented by more than 1. I tested this behaviour with two possible INSERT failures:

  1. an unsatisfied foreign key constraint
  2. an unsatisfied unique constraint obtaining the same result.

Also a point of terminology INSERT IGNORE does not fail it does not insert any rows. It simply has no effect. Finally, a solution: after calling LAST_INSERT_ID() to retrieve an id, reset it to 0, with SET @placeholder := (SELECT LAST_INSERT_ID(0));

Giuliano Gavazzi
  • 1
  • 1