Get all insert ids in a transaction

Question

I have two tables in a MySQL database: "messages" and "message_tags". The "messages" table has an auto increment column "message_id". In Java I want to add a batch of messages to the database using the java.sql package. I want to do this in one transaction to save queries.

My SQL code should look something like this:

START TRANSACTION
INSERT INTO messages(`message`) VALUES ('message1');
INSERT INTO messages_tags(`message_id`, `tags`) VALUES (LAST_INSERT_ID(), 'tagfoo1');
INSERT INTO messages_tags(`message_id`, `tags`) VALUES (LAST_INSERT_ID(), 'tagbar1');
INSERT INTO messages(`message`) VALUES ('message2');
INSERT INTO messages_tags(`message_id`, `tags`) VALUES (LAST_INSERT_ID(), 'tagfoo1');
INSERT INTO messages(`message`) VALUES ('message3');
INSERT INTO messages_tags(`message_id`, `tags`) VALUES (LAST_INSERT_ID(), 'tagfoo2');
...
COMMIT

Is it possible to get all newly generated ids from the messages table back to java in a way, that they can be matched to the original messages? Something like this:

message1 => 1234
message2 => 1235
message3 => 1236
...

Answer 1

you didn't specify if it's static or dynamic, but I guess you can use something like this:

START TRANSACTION
INSERT INTO messages(`message`) VALUES ('message1');
@message1:=last_insert_id();
INSERT INTO messages_tags(`message_id`, `tags`) VALUES (LAST_INSERT_ID(), 'tagfoo1');
@message2:=last_insert_id();
INSERT INTO messages_tags(`message_id`, `tags`) VALUES (LAST_INSERT_ID(), 'tagbar1');
@message3:=last_insert_id();
INSERT INTO messages(`message`) VALUES ('message2');
INSERT INTO messages_tags(`message_id`, `tags`) VALUES (LAST_INSERT_ID(), 'tagfoo1');
@message4:=last_insert_id();
INSERT INTO messages(`message`) VALUES ('message3');
INSERT INTO messages_tags(`message_id`, `tags`) VALUES (LAST_INSERT_ID(), 'tagfoo2');
@message5:=last_insert_id();
...
COMMIT

if you make

select @message1;

the result would be 1234, so you have

@message1 => 1234
@message2 => 1235
@message3 => 1236
...

but you have to specify the SQL variable manually at least you create a procedure, or a function.