Mysql查询执行报错Packet for query is too large (6,831,159 > 4,194,304)

根据意思可以看出 mysql执行的报文过大。需要我们设置允许的最大报文max_allowed_packet；

org.springframework.dao.TransientDataAccessResourceException: 
### Error querying database.  Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (6,831,159 > 4,194,304). 
You can change this value on the server by setting the 'max_allowed_packet' variable
Packet for query is too large (6,831,159 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.;
nested exception is com.mysql.cj.jdbc.exceptions.PacketTooBigException: 
Packet for query is too large (6,831,159 > 4,194,304). 
You can change this value on the server by setting the 'max_allowed_packet' variable.
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:110)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
    at com.sun.proxy.$Proxy137.selectList(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)

 查询MySQL允许的最大报文的大小

SHOW VARIABLES LIKE '%max_allowed_packet%';

通过命令设置允许最大报文为1G

SET GLOBAL max_allowed_packet = 1024*1024*1024;

由于我们项目不能随便更改MySQL配置 ，这里我没有使用这种方法。在项目中我使用的是分批查询，就是把需要查询的所有数据分开，进行多次查询。相当于分页查询。然后把查询的结果存入一个总集合里面。代码如下：

 //分页查询信息
        List<Ment> sumList=new ArrayList<>();
        List<String> externalIdList = list.stream().filter(depart -> Objects.nonNull(depart.getId()))
                .map(MentVo::getId)
                .collect(Collectors.toList());
        int pageNum = externalIdList.size() % pageSize == 0 ? externalIdList.size() / pageSize : (externalIdList.size() / pageSize) + 1;for (int i = 0; i < pageNum; i++) {
            int start = i * pageSize;
            int end = (i + 1) * pageSize > externalIdList.size() ? externalIdList.size() : (i + 1) * pageSize;
            List<Ment> list = mentService
                .lambdaQuery().in(Ment::getExternalId, externalIdList.subList(start, end)).list();
            if (!CollectionUtils.isEmpty(list)){
                sumList.addAll(list);
            }
        }
        if (CollectionUtils.isEmpty(sumList)) {
            sumList = new ArrayList();
        }

记录下容量的运算

解释：

电脑的各种存储器的最小存储单位是比特(bit,简称b也叫位)，8个bit一组构成1个Byte（叫字节）。一般键盘上的每个字符占用2个字节，一个汉字一般占用4个字节。

因为bit或Byte太小了，就有了KB、MB、GB、TB等单位，具体按照如下关系换算：

1B=8b
1KB = 1024B
1MB=1024KB,
1GB = 1024MB
1G = 1024*1024*1024