获取交易中的所有插入ID [英] Get all insert ids in a transaction

问题描述

我在MySQL数据库中有两个表:"messages"和"message_tags". 消息"表具有一个自动递增列"message_id".在Java中，我想使用java.sql包将一批消息添加到数据库中.我想在一个事务中执行此操作以保存查询.

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.

我的SQL代码应如下所示:

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

是否可以通过某种方式将消息表中所有新生成的ID返回给Java，以便它们可以与原始消息匹配?像这样:

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
...

推荐答案

您没有指定它是静态的还是动态的，但是我想您可以使用类似这样的东西:

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

如果您这样做

select @message1;

结果应该是1234，所以你有

the result would be 1234, so you have

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

但是您必须手动指定SQL变量，至少创建一个过程或一个函数.

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

这篇关于获取交易中的所有插入ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！