0

I've found an interesting solution to a problem I have with the 1st answer on this StackOverflow question: 1st answer The problem is that it doesn't work as I expected. If my table is empty, the first bulk insert work correctly but LAST_INSERT_ID returns 1 when the bulk has inserted 100 lines. If I change it and put 100 simple Insert it works. autoinc_loc_mode is set to 1.

Is there a setting to be changed?

Community
  • 1
  • 1
maugch
  • 1,276
  • 3
  • 22
  • 46

1 Answers1

1

What you're experiencing is the expected behavior; from MySQL docs:

If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.

designcise
  • 4,204
  • 1
  • 17
  • 13
  • Oh. you are basically telling me that the linked answer is written upside-down. I should do a last_insert_id()+n instead of -n – maugch May 22 '16 at 12:03
  • @mobinoob that depends on what you want to do with last_insert_id() (you only said you had a problem and found a solution). You usually need last_insert_id if you want to access the data you just inserted. last_insert_id()+n isn't of much use most of the time - do you e.g. want to know max(id)? – Solarflare May 22 '16 at 18:52
  • @Solarflare sorry, you have to check the linked question. I have to do something similar. A java program build the inserts. The last_insert_id is used on that solution as a known id that is deincremented for each step. Imagine a foreach() loop that build those two bulk insert. The solution is either do X insert or transform it to a last_insert_id + N. – maugch May 24 '16 at 17:55
  • @Solarflare tldr; version of what i do: fetch data from internet and then put it on my db with two inserts, one for the item, and one for the relation item-category. – maugch May 24 '16 at 17:56