mysql jdbc PreparedStatement存在65535个参数的限制，大批量提交数据该怎么处理

Viewed 49
2.1

企业微信截图_17322387132011.png
企业微信截图_17322387401169.png
我们现在有一个sql 2000行数据，108列参数，总共216000参数，会被图中的方法给截取变为19392.导致报错。只能用并发处理这个问题么。

3
2 Answers

老师，这个能提供下复现方式，比如表的schema 和 测试数据之类？我们本地复现下

方便的话加我主页微信看看

阿渊@SelectDB6370 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.time.LocalDateTime;

public class DorisTest {

    private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL_PATTERN = "jdbc:mysql://ip:port/ami?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=50&sessionVariables=group_commit=async_mode";
    private static final String USER = "root";
    private static final String PASSWD = "";
    private static final int INSERT_BATCH_SIZE = 50000;

    public static void main(String[] args) throws Exception {
        Class.forName(JDBC_DRIVER);
        // add rewriteBatchedStatements=true and cachePrepStmts=true in JDBC url
        // set session variables by sessionVariables=group_commit=async_mode in JDBC url
        try (Connection conn = DriverManager.getConnection(
                URL_PATTERN, USER, PASSWD)) {

            String query = "insert into device_data_test (create_time,device_no) values(?, ?)";
            try (PreparedStatement stmt = conn.prepareStatement(query)) {
                for (int j = 0; j < 5; j++) {
                    // 10 rows per insert
                    for (int i = 0; i < INSERT_BATCH_SIZE; i++) {
                        stmt.setTimestamp(1, java.sql.Timestamp.valueOf(LocalDateTime.now()));
                        stmt.setString(2, "name" + j*5 + i);
                        stmt.addBatch();
                    }
                    int[] result = stmt.executeBatch();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}


CREATE TABLE device_data_test
(
    create_time DATETIME(6) NOT NULL,
    device_no    VARCHAR(50) NOT NULL

) DISTRIBUTED BY HASH(`device_no`) BUCKETS AUTO
PROPERTIES (
    "replication_num" = "3",
    "estimate_partition_size" = "1G"
);

schema和代码

3