Map auto increment ids on multi-row insert I have the following table with an AUTO_INCREMENT column. 

CREATE TABLE animals ( 
  id MEDIUMINT NOT NULL AUTO_INCREMENT, 
  name CHAR(30) NOT NULL, 
  PRIMARY KEY (id) 
); 


Usually I load multiple rows with one INSERT like this: 

INSERT INTO animals (name) 
VALUES  ('dog'),('cat'),('cow'); 


Sometimes though I want assign to each value its ID. Like this: 

1 -&gt; dog 
2 -&gt; cat 
3 -&gt; cow 


If I insert simple values I can select them back. But when I insert JSON documents I'm worried about using syntax like SELECT WHERE json IN (...). 

I thought to get MAX(id) before selection, then get last_insert_id after selection and using count of inserted rows assign id to each. But I haven't found in documentation anything about order that rows in insert are processed, so I'm worried I won't be able to know what the ID's are.

Inserting rows one by one and get last_insert_id for each is not good solution. Is there a way to insert multiple rows at once and return to the application the IDs for each row?
Don't use MAX(id), that could be wrong if to another connection doing inserts.  last_insert_id, on the other hand, is local to the session, so it is unaffected by other connections.

INSERT INTO animals (name) 
    VALUES  ('dog'),('cat'),('cow');
SELECT last_insert_id();  -- Let's say you get 34
-- Then dog=34, cat=35, cow=36


But -- beware of innodb_autoinc_lock_mode -- the setting of this can affect what I discussed above.  This is especially true if, for whatever reason, some of the rows fail to insert.  (Example: you have UNIQUE(name) and already have cat in the table.)  In at least one of the modes, 35 is 'burned'.

The following goes beyond what you have asked for...

I decided is the best way to deal with a similar problem involves two steps.  First, let me explain what is going on.  I am 'normalizing' the names of animals.  Instead of using "dog", I want to use the id associated with dog.  I have lots of data coming in, and I want to batch insert a bunch of animal names into the table, plus get the ids for them.  I am very likely to have lots of dups, but I don't want to 'burn' an id for every dup.

So...

INSERT ... SELECT ... LEFT JOIN ...  -- insert only the 'new' animals.   
UPDATE ... JOIN ...  -- pull the ids into another table.


The details are spelled out here .