Java 带有 rewriteBatchedStatements=true 的 MySQL 和 JDBC
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26307760/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL and JDBC with rewriteBatchedStatements=true
提问by dazito
I've been reading around, here, hereand hereabout the advantages of using rewriteBatchedStatements=true
我一直在阅读这里，这里和这里关于使用的优点rewriteBatchedStatements=true
If I understood it right, with rewriteBatchedStatements=truethe JDBC will packas many queries as possible into a single network packet, lowering this way the network overhead. Am I right?
如果我理解正确的话，rewriteBatchedStatements=trueJDBC 会将尽可能多的查询打包到一个网络数据包中，从而降低网络开销。我对吗？
Then it comes into my attention that the value defined in the MySQL server for the max_allowed_packetmay cause problems with the queries (queries not being executed on the server).
然后我注意到在 MySQL 服务器中定义的值max_allowed_packet可能会导致查询出现问题（查询不在服务器上执行）。
So my second question is, does JDBC knows the value assigned to max_allowed_packetand therefore make the packet smaller than the defined value for max_allowed_packetor that is something that the developer has to take in consideration?
所以我的第二个问题是，JDBC 是否知道分配给的值max_allowed_packet并因此使数据包小于定义的值，max_allowed_packet或者这是开发人员必须考虑的事情？
If I understood something wrong, please let me know as well.
如果我理解错了，也请告诉我。
采纳答案by Gord Thompson
with rewriteBatchedStatements=true the JDBC will pack as many queries as possible into a single network packet, lowering this way the network overhead. Am I right?
使用 rewriteBatchedStatements=true JDBC 将尽可能多的查询打包到单个网络数据包中，从而降低网络开销。我对吗？
Yes. The following code
是的。以下代码
String myConnectionString =
"jdbc:mysql://localhost:3307/mydb?" +
"useUnicode=true&characterEncoding=UTF-8";
try (Connection con = DriverManager.getConnection(myConnectionString, "root", "whatever")) {
try (PreparedStatement ps = con.prepareStatement("INSERT INTO jdbc (`name`) VALUES (?)")) {
for (int i = 1; i <= 5; i++) {
ps.setString(1, String.format(
"Line %d: Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.",
i));
ps.addBatch();
}
ps.executeBatch();
}
}
will send individual INSERT statements even though I have created a Batch
即使我创建了一个批处理，也会发送单独的 INSERT 语句
INSERT INTO jdbc (`name`) VALUES ('Line 1: Lorem ipsum ...')
INSERT INTO jdbc (`name`) VALUES ('Line 2: Lorem ipsum ...')
However, if I change the connection string to include rewriteBatchedStatements=true
但是，如果我将连接字符串更改为包括 rewriteBatchedStatements=true
String myConnectionString =
"jdbc:mysql://localhost:3307/mydb?" +
"useUnicode=true&characterEncoding=UTF-8" +
"&rewriteBatchedStatements=true";
then JDBC will send one or more multi-row INSERT statements
然后 JDBC 将发送一个或多个多行 INSERT 语句
INSERT INTO jdbc (`name`) VALUES ('Line 1: Lorem ipsum ...'),('Line 2: Lorem ipsum ...')
does JDBC knows the value assigned to max_allowed_packet and therefore make the packet smaller than the defined value for max_allowed_packet ... ?
JDBC 是否知道分配给 max_allowed_packet 的值，从而使数据包小于 max_allowed_packet 的定义值...？
Yes. If you enable the MySQL general log and check it you will see that MySQL Connector/J inspects a bunch of variables when it connects, one of which is max_allowed_packet. You can also set a small max_allowed_packetvalue and verify that JDBC splits a batch into several multi-row INSERT statements if a single such statement for the whole batch would exceed max_allowed_packet.
是的。如果您启用 MySQL 常规日志并检查它，您将看到 MySQL Connector/J 在连接时检查一堆变量，其中一个是max_allowed_packet. 您还可以设置一个小的max_allowed_packet值并验证 JDBC 是否将一个批处理拆分为多个多行 INSERT 语句，如果整个批处理的单个此类语句超过max_allowed_packet.
