根据意思可以看出 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 sumList=new ArrayList(); List 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 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=8b1KB = 1024B1MB=1024KB,1GB = 1024MB1G = 1024*1024*1024