0

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?

  • 2
    Why you create autoincrement field and, from the other hand, do not want to use its functionality. Explain more thoroughly WHY you want to assign IDs values "by hands". – Akina Dec 11 '18 at 12:33
  • I want to use it functionality. Evidently, it is trouble with my explanation. I need to get back from MySQL to my server code generated ids. But not only ids - i need to know for each value what ID it got. – Drewblin Dec 11 '18 at 12:58
  • i insert JSON documents Unclear. You insert whole document into TEXT type field? You load it by LOAD DATA INFILE and use post-parsing? You do something else? How you perform it with multiple files in one query? i need to know for each value what ID it got You know inserted data. Use it in WHERE and obtain ID. PS. It seems you describe not the task but the way you decide to solve your (unknown for us yet) task. Are you sure you select the best way? – Akina Dec 11 '18 at 13:22
0

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 .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.