I have the following table with an
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?