当rewriteBatchedStatements=true时,JDBC会尽可能多地将查询打包成单个网络数据包,从而降低网络开销。我说的对吗?
是的。下面的代码
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();
}
}
即使我已经创建了批处理,它仍会发送单个INSERT语句。
INSERT INTO jdbc (`name`) VALUES ('Line 1: Lorem ipsum ...')
INSERT INTO jdbc (`name`) VALUES ('Line 2: Lorem ipsum ...')
然而,如果我更改连接字符串以包括rewriteBatchedStatements=true
String myConnectionString =
"jdbc:mysql://localhost:3307/mydb?" +
"useUnicode=true&characterEncoding=UTF-8" +
"&rewriteBatchedStatements=true";
然后JDBC将发送一个或多个多行INSERT语句
INSERT INTO jdbc (`name`) VALUES ('Line 1: Lorem ipsum ...'),('Line 2: Lorem ipsum ...')
JDBC是否知道分配给max_allowed_packet的值并因此使数据包小于max_allowed_packet的定义值...?
是的。如果您启用了MySQL常规日志并检查它,您会发现MySQL Connector/J在连接时检查了一堆变量之一是max_allowed_packet
。您还可以设置一个小的max_allowed_packet
值,验证JDBC将批处理拆分为多个多行INSERT语句,如果整个批处理的单个语句超过max_allowed_packet
。