How do I get the ID of multiple inserted rows in MySQL?

mysql sql database insert normalization
Googlebot picture Googlebot · Sep 21, 2011 · Viewed 54.9k times · Source

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".

Answer

glglgl picture glglgl · Sep 21, 2011

  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.

Related questions

How to do a batch insert in MySQL

I have 1-many number of records that need to be entered into a table. What is the best way to do this in a query? Should I just make a loop and insert one record per iteration? Or is there …

Read More
MySQL 5.6 Error 1054: Unknown column in 'field list' on INSERT

I'm using MySQL Workbench to create and manage a comic book store database. I'm having a problem inserting rows into the COMIC_ISSUE table. The problem I'm having is the same problem many others posted questions about here only none …

Read More
'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More