使用mybatis的插入的语句过长报错，能不能改成循环插入，如何改成分批插入呀？

Question

不想改变服务端的“max_allowed_packet”，能不能从代码入手？

Caused by: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (66,171,787 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.

    <insert id="insertBase" parameterType="List">
        insert  gascharge_data (autoid) values
        <foreach collection="list"  separator=","  item="item" >
            (
            #{item.autoid},
            #{item.barcode},
            #{item.modeltype},
            #{item.modelname},
            #{item.modelcode},
            #{item.vacuumdown},
            #{item.vacuumup},
            #{item.vacuumtime},
            #{item.begintime},
            #{item.endtime},
            #{item.setvolume},
            #{item.realvolume},
            #{item.status},
            #{item.gun},
            #{item.line},
            #{item.uploadstatus},
            #{item.cvsstatus},
            #{item.timestr},
            #{item.valid},
            #{item.uptime},
            #{item.usercode},
            #{item.modelspec},
            #{item.svacuumtime},
            #{item.readok}
            )
        </foreach>


    </insert>

private void synchronizeGaschargeData(){
         try {
            //查询本地库最新的id
             RefrigerantDTO refrigerant =refrigerantMapper.getMaxAutoId();
             log.info("查询本地库最新的id，refrigerant："+refrigerant);
             Integer autoid = 0;

             if (refrigerant!=null&&refrigerant.getAutoid()!=null){
                autoid = refrigerant.getAutoid();
             }
            conn = getConnection();
            st = conn.createStatement();
            String sql = "select * from gascharge_data where autoid > '"+autoid+"'";
            log.info("远程库查询sql："+sql);
            rs = st.executeQuery(sql);
            List<RefrigerantDTO> RefrigerantList = new ArrayList<>();
            while(rs.next()){
                RefrigerantDTO ref = new RefrigerantDTO();
                ref.setAutoid(rs.getInt("AutoId"));
                ref.setBarcode(rs.getString("BarCode"));
                ref.setModeltype(rs.getString("ModelType"));
                ref.setModelname(rs.getString("ModelName"));
                ref.setModelcode(rs.getString("ModelCode"));
                ref.setVacuumdown(rs.getString("VacuumDown"));
                ref.setVacuumup(rs.getString("VacuumUp"));
                ref.setVacuumtime(rs.getString("VacuumTime"));
                ref.setBegintime(rs.getString("BeginTime"));
                ref.setEndtime(rs.getString("EndTime"));
                ref.setSetvolume(rs.getString("SetVolume"));
                ref.setRealvolume(rs.getString("RealVolume"));
                ref.setStatus(rs.getString("Status"));
                ref.setGun(rs.getString("Gun"));
                ref.setLine(rs.getString("Line"));
                ref.setUploadstatus(rs.getString("UpLoadStatus"));
                ref.setCvsstatus(rs.getString("CVSStatus"));
                ref.setTimestr(rs.getString("TimeStr"));
                ref.setValid(rs.getString("Valid"));
                ref.setUptime(rs.getString("UpTime"));
                ref.setUsercode(rs.getString("UserCode"));
                ref.setModelspec(rs.getString("ModelSpec"));
                ref.setSvacuumtime(rs.getString("SVacuumTime"));
                ref.setReadok(rs.getString("readok"));
                RefrigerantList.add(ref);
            }
             refrigerantMapper.insertBase(RefrigerantList);

        }catch (Exception e){
            log.info("同步GaschargeData表失败",e);
        }

    }

Answer 1

可以分批次插入，比如一次插入1000条。比如定义一个num，每循环一次就+1，将num对1000取模，如果为0就批量插入，并清空list，如此反复