39

I am inserting some words into a two-column table with this command:

INSERT IGNORE INTO terms (term) VALUES ('word1'), ('word2'), ('word3');

  1. How can I get the ID (Primary Key) of the row in which each word is inserted. I mean returning a value like "55,56,57" after executing INSERT. Does MySQL have such a response?

  2. The term column is UNIQUE. If a term already exists, MySQL will not insert it. Is it possible to return the reference for this duplication (i.e. the ID of the row in which the term exists)? A response like "55,12,56".

ADTC
  • 8,999
  • 5
  • 68
  • 93
Googlebot
  • 15,159
  • 44
  • 133
  • 229

3 Answers3

37

  1. You get it via SELECT LAST_INSERT_ID(); or via having your framework/MySQL library (in whatever language) call mysql_insert_id().

  2. That won't work. There you have to query the IDs after inserting.

glglgl
  • 89,107
  • 13
  • 149
  • 217
  • 2
    Right - not in one with `INSERT IGNORE`. But in a non-`IGNORE` one, it gives you the first value generated and inserted. – glglgl Sep 21 '11 at 14:30
  • 1
    What if another user has made another insert in the meanwhile? – Pitto Jan 15 '15 at 10:05
  • 3
    @Pitto Nevertheless you would get the correct value from `LAST_INSERT_ID()` resp. `mysql_insert_id()`. The former knows what has last happened in the current session, the latter is directly bound to the last query. If something else happens in a different connection, that doesn't hurt. – glglgl Jan 15 '15 at 10:06
1

Why not just:

SELECT ID
  FROM terms
 WHERE term IN ('word1', 'word2', 'word3')
mellamokb
  • 56,094
  • 12
  • 110
  • 136
  • 2
    That'd fetch terms that were previously inserted, and it'd be vulnerable to a race condition where something else inserts records before the select query fires. – ceejayoz Sep 21 '11 at 14:28
  • 2
    ?? term has a UNIQUE condition. The only thing that could affect the SELECT in a race condition is a DELETE, not another INSERT. And according to (2) in OP's post, they want the previously inserted records' IDs to be included. – mellamokb Sep 21 '11 at 14:30
  • That's what I had in mind; but it needs few lines of coding. Apart from SELECT, I need fetch the results and get them in a while loop (in php that I use). I hope to find some kind of mysql response. – Googlebot Sep 21 '11 at 14:53
  • @Googlebot this is the best way to go about it. If you want to iterate over the IDs, just execute a `SELECT` (shown in this answer) and iterate over the results. _What's in a few more lines of coding anyway?_ If you really want MySQL to echo the ID, you have to issue separate single `INSERT` statements and execute `SELECT LAST_INSERT_ID()` after each `INSERT` to collect the IDs into an array. – ADTC Oct 17 '17 at 05:34
0

First, to get the id just inserted, you can make something like :

SELECT LAST_INSERT_ID() ;

Care, this will work only after your last INSERT query and it will return the first ID only if you have a multiple insert!

Then, with the IGNORE option, I don't think that it is possible to get the lines that were not inserted. When you make an INSERT IGNORE, you just tell MySQL to ignore the lines that would have to create a duplicate entry.

If you don't put this option, the INSERT will be stopped and you will have the line concerned by the duplication.

ADTC
  • 8,999
  • 5
  • 68
  • 93
BMN
  • 8,253
  • 14
  • 48
  • 80