在MySQL中使用带有Connector / J的批处理插入的getGeneratedKeys [英] Using getGeneratedKeys with batch inserts in MySQL with Connector/J

问题描述

使用Connector / J，我想对主表执行批处理插入，然后批处理插入详细信息表（ PreparedStatement.executeBatch（） ）。我没有在网上找到很多信息，所以我正在寻找有这方面经验的人的任何反馈。

1. 我可以使用
   Statement.getGeneratedKeys（）获取
   主插入行中新插入的行
   的ID，以便我可以
   将它们用作
   细节插入中的外键？




2. 如果不是每个查询导致
   一个插入（例如有一个 insert
ignore code> insert ... on duplicate
   key update query）？我会在 Statement.getGeneratedKeys（）中为每个
   语句或者仅为新的语句获得一行
   ？




3. 
   将会有什么结果Statement.getGeneratedKeys（） return
   $ b之一有一个错误$ b插入主记录，并在连接字符串中将
   continueBatchOnError 设置为 true
   ？




4. Connector / J版本5.0.x和5.5.x之间的相关行为是否有任何差异？关于MySQL 5.0 vs 5.1？




5. 有任何其他问题或困扰我应该注意吗？

$ b $
好吧，我跑了一些测试。对于Connector / J 5.1和MySQL 5.1.42，我观察到以下情况：



1. Statement.getGeneratedKeys 如果插入或更新了行（更新计数数组返回 executeBatch（）返回'1'或'2'）， Statement.getGeneratedKeys（）如果没有修改该行（ insert ignore 或 insert ...对重复键更新 op， executeBatch（）返回 3 ），没有键。




2. getGeneratedKeys 返回的ResultSet将具有成功插入行的条目，如（2）。对于失败的插入，不会有生成的键行（更新计数值 Statement.EXECUTE_FAILED ）




3. ？




4. 请谨慎使用JDBC连接字符串中的 rewriteBatchedStatements 。如果设置为 true ，任何失败都将导致重写的chunk中的每一行都被视为失败。




一种处理这种情况的方法是迭代失败的行并重试它们， ol>


Using Connector/J, I would like to do a batch insert into a master table followed by a batch insert into a details table (PreparedStatement.executeBatch() for both). I haven't found much information online, so I'm looking for any feedback from people that have experience with this.

1. Can I use Statement.getGeneratedKeys() to get the IDs of the newly inserted rows in the master table so that I can use them as foreign keys in the detail inserts?

2. What if not every query resulted in an insert (e.g. there was an insert ignore or insert ... on duplicate key update query)? Will I get a row in Statement.getGeneratedKeys() for every statement, or only for the new ones?

3. What will Statement.getGeneratedKeys() return there is an error with one of the inserted master records, and continueBatchOnError is set to true in the connection string?

4. Are there any differences in the related behavior between Connector/J versions 5.0.x vs 5.5.x? What about MySQL 5.0 vs 5.1?

5. Any there any other issues or gotchas that I should be aware of?

6. Is there a better way to do this?

解决方案

Well, I ran some tests. With Connector/J 5.1 and MySQL 5.1.42, I observe the following:

1. Statement.getGeneratedKeys() works as expected for inserts

2. If a row was inserted or updated (the update count array returned by executeBatch() returns '1' or '2'), Statement.getGeneratedKeys() will have the key for that row. If the row was not modified (insert ignore or insert ... on duplicate key update that results in a no-op, executeBatch() returns 3), there is no key.

3. The ResultSet returned by getGeneratedKeys will have the entries for successfully inserted rows, as per (2). There will not be a generated key row for the failed inserts (where update count value is Statement.EXECUTE_FAILED)

4. ?

5. Be careful with rewriteBatchedStatements in the JDBC connection string. If it is set to true, any failures will result in every row in the rewritten "chunk" to be treated as though it had failed. One way to handle this is to iterate the failed rows and retry them without batching.

6. ?

这篇关于在MySQL中使用带有Connector / J的批处理插入的getGeneratedKeys的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！