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
– user2864740, Commented Jun 16, 2014 at 9:08
LAST_INSERT_ID() returns the insert id of one record. cant be used with multiple records insertion! — deviloper
– deviloper, Commented Jun 16, 2014 at 9:09
You can use triggers to insert row on each new entry to table — Justinas
– Justinas, Commented Jun 16, 2014 at 9:11

vhu · Accepted Answer · 2014-06-16 09:13:31Z

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.

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

Collectives™ on Stack Overflow

Multiple Inserted Row Ids

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related