批量插入MySQL遇max_allowed_packet超限（Google API取数）求解决办法

针对你遇到的max_allowed_packet超限问题，结合不能改表结构、DBA拒绝调参数、需兼顾效率的约束，给你几个实操性的解决办法：

1. 单条插入+事务批量提交 #

把原来的批量PreparedStatement拆成单条执行，但将多条操作打包到一个事务中提交，既避免单条事务的开销，又保证每个数据包的大小不会超限。

示例代码：

Connection conn = null;
PreparedStatement pstmt = null;
try {
    conn = getConnection();
    conn.setAutoCommit(false); // 关闭自动提交，开启事务
    String sql = "INSERT INTO your_table (id, json_data, ...) VALUES (?, ?, ...) ON DUPLICATE KEY UPDATE json_data = VALUES(json_data), ...";
    pstmt = conn.prepareStatement(sql);

    int batchSize = 1000; // 每1000条提交一次事务
    int count = 0;

    for (YourData data : dataList) {
        pstmt.setString(1, data.getId());
        pstmt.setString(2, data.getJsonData());
        // 设置其他参数...
        
        pstmt.executeUpdate(); // 单条执行
        count++;

        if (count % batchSize == 0) {
            conn.commit(); // 批量提交事务
        }
    }
    // 提交剩余的记录
    if (count % batchSize != 0) {
        conn.commit();
    }
} catch (SQLException e) {
    if (conn != null) {
        try {
            conn.rollback(); // 事务回滚
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    e.printStackTrace();
} finally {
    // 关闭资源
    if (pstmt != null) pstmt.close();
    if (conn != null) conn.close();
}

2. 压缩JSON字段内容 #

在插入前将JSON字符串用GZIP压缩，转成Base64字符串存入原JSON字段（无需改表结构），读取时再解码解压。这个方法能大幅缩小单条数据的体积，原大JSON可能压缩到原来的10%-20%，批量500条也不会触发数据包超限。

注意：需确认依赖该表的其他程序是否能适配这种压缩后的JSON格式，若可以，这是最有效的解决方案。

压缩示例代码：

public static String compressJson(String json) throws IOException {
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    try (GZIPOutputStream gzip = new GZIPOutputStream(baos)) {
        gzip.write(json.getBytes(StandardCharsets.UTF_8));
    }
    return Base64.getEncoder().encodeToString(baos.toByteArray());
}

// 读取时解压（供依赖程序使用）
public static String decompressJson(String compressedBase64) throws IOException {
    byte[] compressedBytes = Base64.getDecoder().decode(compressedBase64);
    ByteArrayInputStream bais = new ByteArrayInputStream(compressedBytes);
    try (GZIPInputStream gzip = new GZIPInputStream(bais);
         BufferedReader reader = new BufferedReader(new InputStreamReader(gzip, StandardCharsets.UTF_8))) {
        StringBuilder sb = new StringBuilder();
        String line;
        while ((line = reader.readLine()) != null) {
            sb.append(line);
        }
        return sb.toString();
    }
}

插入时调用compressJson处理JSON数据后存入字段即可。

3. 优化ON DUPLICATE UPDATE语句 #

检查你的ON DUPLICATE UPDATE部分，只保留业务上必须更新的字段，减少每条SQL语句的长度，从而降低批量数据包的总大小。

比如原来的语句可能更新多个字段：

INSERT INTO table (a, b, c, json_data) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE a=VALUES(a), b=VALUES(b), c=VALUES(c), json_data=VALUES(json_data)

若业务上仅需更新json_data，可简化为：

INSERT INTO table (a, b, c, json_data) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE json_data=VALUES(json_data)

这样每条SQL的长度会大幅缩短，批量插入时的总数据包大小也会降低。

4. 按客户+日期切片分批次处理 #

不要一次性拉取某个客户90天的数据，而是按单个客户+单天的维度拆分拉取和插入。每个批次只处理一个客户一天的Insights数据，单批数据量会非常小，完全不会触发数据包超限。同时可用线程池并行处理不同客户，保证整体效率。

操作逻辑：

• 遍历20个客户
• 对每个客户，遍历90天的日期
• 每个客户+日期的组合单独调用Google API拉取数据，再批量插入（哪怕批量1000条也不会超）
• 用线程池控制并发数，比如同时处理5个客户的日期数据

这样既能控制单批数据量，又能通过并发保证整体处理效率。

内容的提问来源于stack exchange，提问作者Shila Mosammami