Map auto increment ids on multi-row insert

Question

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 -> dog 
2 -> cat 
3 -> 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?

Answer 1

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 .