Multiple Inserted Row Ids

Question

I am inserting multiple rows at once like:

INSERT INTO person VALUES ('joe', 50), ('jon', 24);

I then need to use their id to link the above to another table. Normally I would do that by using LAST_INSERT_ID()

INSERT INTO hobbies VALUES (LAST_INSERT_ID(), "golf");

but that's not viable with inserting multiple values as LAST_INSERT_ID() returns the id of the first inserted row.

I could just increment LAST_INSERT_ID() after each hobby insertion but that assumes that all people rows were inserted successfully.

The other option is to just insert the people rows one at a time but I don't know whether that's a performance hit?

Answer 1

Insert the values using single statements and wrap them into a transaction, e.g:

START TRANSACTION;
INSERT INTO person  VALUES ('joe', 50);
INSERT INTO hobbies VALUES (LAST_INSERT_ID(),'golf');
COMMIT;

You may take a slight performance hit but this should give you consistent results. Incrementing the value returned by LAST_INSERT_ID() is not safe as there may have been concurrent inserts that modified the AUTO INCREMENT value.