I want to insert multiple rows into a table, using a single INSERT statement. This is no problem, since SQL offers the option to provide multiple rows as parameter for a single INSERT statement. Now, those rows contain an ID field that is incremented automatically, i.e. its value is set by the database, not by my code.
As a result, I would like to get the ID values of the inserted rows. My basic question is: How do I do that for MariaDB / MySQL?
As it turns out, this is pretty simple, e.g. in PostgreSQL, as PostgreSQL has the RETURNING clause for INSERT which returns the desired values for one or even for multiple rows. This is exactly what I want and it works.
Unfortunately, neither MariaDB nor MySQL have PostgreSQL's RETURNING clause, so I need to fallback to something such as LAST_INSERT_ID(), but this only returns the ID of the single last inserted row, even if multiple rows were inserted using a single INSERT. How do I get all the ID values?
My code currently looks like this:
INSERT INTO mytable
(foo, bar)
VALUES
('fooA', 'barA'),
('fooB', 'barB');
SELECT LAST_INSERT_ID() AS id;
How can I solve this issue in a way that works even with concurrent writes?
(And no, it's not an option to change to a UUID field, or something like this; the auto-increment field is given, and can not be changed.)
MySQL & MariaDB have the LAST_INSERT_ID() function, and it returns the id generated by the most recent INSERT statement in your current session.
But when your INSERT statement inserts multiple rows, LAST_INSERT_ID() returns the first id in the set generated.
In such a batch of multiple rows, you can rely on the subsequent id's being consecutive. The MySQL JDBC driver depends on this, for example.
If the rows you insert include a mix of NULL and non-NULL values for the id column, you have a risk of messing up this assumption. The JDBC driver returns the wrong values for the set of generated id's.