带有 rewriteBatchedStatements=true 的 MySQL 和 JDBC [英] MySQL and JDBC with rewriteBatchedStatements=true

问题描述

我一直在阅读，此处，此处 和此处关于使用rewriteBatchedStatements=true

的优点

如果我理解正确的话，使用 rewriteBatchedStatements=true，JDBC 会将尽可能多的查询打包到一个网络数据包中，从而降低网络开销.我说得对吗?

然后我注意到 MySQL 服务器中为 max_allowed_pa​​cket 定义的值可能会导致查询出现问题(查询不在服务器上执行).

所以我的第二个问题是，JDBC 是否知道分配给 max_allowed_pa​​cket 的值并因此使数据包小于 max_allowed_pa​​cket 的定义值，或者这是开发人员必须考虑?

如果我理解错了，也请告诉我.

解决方案

使用 rewriteBatchedStatements=true 时，JDBC 将尽可能多的查询打包到单个网络数据包中，从而降低网络开销.我说得对吗?

是的.以下代码

String myConnectionString =jdbc:mysql://localhost:3307/mydb?"+"useUnicode=true&characterEncoding=UTF-8";试试 (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(第 %d 行:Lorem ipsum dolor sat amet，consectetur adipisicing elit，sed do eiusmod tempor incididunt ut laboure et dolore magna aliqua.Ut enim ad minim veniam，quis nostrud exercitation ullamcolaboris nisi ut aliquip ex cond一世));ps.addBatch();}ps.executeBatch();}}

即使我已经创建了一个 Batch 也会发送单独的 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_pa​​cket 的值，从而使数据包小于为 max_allowed_pa​​cket 定义的值...?

是的.如果您启用 MySQL 常规日志并检查它，您将看到 MySQL Connector/J 在连接时检查一堆变量，其中一个是 max_allowed_pa​​cket.您还可以设置一个小的 max_allowed_pa​​cket 值并验证 JDBC 是否将一个批次拆分为多个多行 INSERT 语句，如果整个批次的单个此类语句超过 max_allowed_pa​​cket.

I've been reading around, here, here and here about the advantages of using rewriteBatchedStatements=true

If I understood it right, 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?

Then it comes into my attention that the value defined in the MySQL server for the max_allowed_packet may cause problems with the queries (queries not being executed on the server).

So my second question is, does JDBC knows the value assigned to max_allowed_packet and therefore make the packet smaller than the defined value for max_allowed_packet or that is something that the developer has to take in consideration?

If I understood something wrong, please let me know as well.

解决方案

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?

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

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

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

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

这篇关于带有 rewriteBatchedStatements=true 的 MySQL 和 JDBC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！