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?

Just use multiple statements and insert each person separately. As long as they are within the *same transaction* the performance should be similar. — user2864740, Jun 16 '14 at 09:08
LAST_INSERT_ID() returns the insert id of one record. cant be used with multiple records insertion! — M Reza Saberi, Jun 16 '14 at 09:09
You can use triggers to insert row on each new entry to table — Justinas, Jun 16 '14 at 09:11

score 1 · Accepted Answer · answered Jun 16 '14 at 09:13

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.

answered Jun 16 '14 at 09:13

vhu

12,244
11
38
48

1

It's not ideal but you could lock the table and then you'd be sure that you could get a range of auto_increment values – James C Jun 16 '14 at 09:16

Multiple Inserted Row Ids

1 Answers1

Linked