MySQL - Return group of last inserted ID's

Question

So theres lots of questions and answers about LAST_INSERT_ID(). This works fine if you're inserting a single row and need the return value of that ID to do stuff with, but what if you are doing a group insert and need the return values of all AUTO_INCREMENT ID's?

Take this table:

Name     |   Data1    |    Data2    |      Data3
---------------------------------------------------
Name 1   |   blah     |    Herp     |      Derp
Name 2   |   rwwe     |    asdf     |      3445
Name 3   |   asdf     |    asdf     |      8678
Name 1   |   guih     |    adsf     |      3565
Name 1   |   asfg     |    rwer     |      7877

I need to insert the value of Name 1 into a table, and then take that primary key and insert the values from Data1, 2 and 3 into other tables that have a primary key constraint over them.

The problem is that "Name" is non-unique so I can't query back the original/staging table and get the values that way.

As per the documentation, last_insert_id() operates in the context of a single INSERT statement. I tested it anyway.

enter image description here

I also tried writing the value back into the table, but of course SQL operates on sets:

insert into test_fetch_id(lastname,Last_ID)
select RESOURCE_ID, last_insert_id() from resources r limit 100;

Giving me: enter image description here

Any help would be greatly appreciated.

If you can't get the range of ids from the last inserted id to the last inserted minus the number of affected rows, you can take the last insert after each insert call and store it somewhere outside of MySQL. — Shomz, Jul 16 '14 at 11:23
Hi Shromz, its a single insert statement. (This is the problem). Unless I'm missing something? — Jamie S, Jul 16 '14 at 11:28
AFAIK this is impossible. The last id method can only return the single last id inserted. — ToBe, Jul 16 '14 at 11:39
There you go: http://stackoverflow.com/questions/8481421/does-a-mysql-multi-row-insert-grab-sequential-autoincrement-ids — didierc, Jul 16 '14 at 14:55
If you are in the right setup, just getting `last_insert_id` and knowing how many rows you inserted is enough. Otherwise you might need an extra column to store a "group insert id" which will let you select what you inserted later on. — didierc, Jul 16 '14 at 14:57
This is good information but the solution is not that simple - the original insert which the primary key is generated produced from is actually 2 pieces of non-unique data. I need to act on this key immediately and move to the next one, the way in which these results are inserted isn't deterministic. I've got a solution that runs quite well using a cursor over ~2000 rows (test), but I need to scale this out to 400,000 so I'm looking for a group method. — Jamie S, Jul 16 '14 at 17:58
Maybe, just MAYBE, you are looking for a trigger solution. See mysql trigger documentation, especially "AFTER INSERT" one. — David162795, Nov 06 '14 at 12:54

score 0 · Accepted Answer · answered Nov 14 '14 at 09:34

Well, it turns out that MySQL is... painful to work with, however if anyone wants a solution here it is:

You need to create a cursor, and set its value to last_insert_id(). For example:

    declare last_insert_pk int;
    declare last_insert2_pk int;

Then, in the cursor, you set the last inserted pk(s) for that iteration:

    set last_insert_pk = last_insert_id();
    -- ...some stuff...
    set _insert2_pk = last_insert_id();

I had to use 8 different primary keys in a giant relation table, however it worked really well. There may be a better way, but this is understandable and repeatable.

Good luck!

MySQL - Return group of last inserted ID's

1 Answers1