带有Statement.RETURN_GENERATED_KEYS的MySQL批处理stmt [英] MySQL batch stmt with Statement.RETURN_GENERATED_KEYS

问题描述

我试图批量执行2个sql语句。第一个语句是一个插件，它为其ID使用自动生成的值。第二个语句是插入另一个表，但它需要使用上面的自动生成的值作为插入值的一部分

I am trying to execute 2 sql statements in a batch. the first statement is an insert that uses a auto generated value for its ID. the second statement is an insert to another table but it needs to use the auto generated value from above as part of the insert value

之类的东西（其中id只是为了表示自动生成的字段未在sql中定义

something like (where id is just to show the auto generated field its not defined in sql

stmt.addbatch(insert into table1("id_auto_generated", "foo"));
stmt.addbatch(insert into table2("table1_id", "boo"));

<我现在的方式就是在我的第二个sql中使用它

the way I do it now is by using this in my second sql

insert into table2(LAST_INSERT_ID(), "boo");

问题是即使在批处理语句中它也很慢，因为我的批处理可以是50,000个插入。

Problem is its slow even in batch statements its very slow as my batch can be 50,000 inserts.

我想切换到预准备语句，但不知道如何将Statement.RETURN_GENERATED_KEYS或LAST_INSERT_ID（）与预准备语句一起使用。

I wanted to switch to prepared statements but do not know how to use Statement.RETURN_GENERATED_KEYS or LAST_INSERT_ID() with prepared statements.

推荐答案

我不确定这是否可以通过 addBatch 来完成此操作，除非你以这种方式 正在使用。另一件要尝试的是放弃 addBatch（）方法并尝试关闭自动提交。然后你可以使用 stmt.getGeneratedKeys（）; 。类似于：

I'm not sure this is a way you can do this with addBatch except in the manner that you are using. Another thing to try is to abandon the addBatch() method and try turning off auto-commit instead. Then you can use the stmt.getGeneratedKeys();. Something like:

connection.setAutoCommit(false);
stmt.executeUpdate("insert into table1(\"id_auto_generated\", \"foo\") ...");
DatabaseResults results = stmt.getGeneratedKeys();
// extract the id from the results
stmt.executeUpdate("insert into table2(\"table1_id\", \"boo\") ...");
... many more stmts here
connection.commit();
connection.setAutoCommit(true);

希望这有帮助。

这篇关于带有Statement.RETURN_GENERATED_KEYS的MySQL批处理stmt的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！