项目场景:
Mysql不限制显示条数查询数据库:
SELECT * FROM orange LIMIT 0,2147483642;
Integer.MAX_VALUE-5 = 2147483642。
数据库报错信息:
Lost connection to MySQL server during query
问题描述:
### Error querying database. Cause: java.sql.SQLException: connection disabled
### The error may exist in URL [jar:file:/D:/Software/maven/maven_repository/com/ztesoft/bss/base-dataservice/9.0.25(1)/base-dataservice-9.0.25(1).jar!/com/ztesoft/bss/base/mapper/CommonQueryMapper.xml]
### The error may involve com.ztesoft.bss.base.mapper.CommonQueryMapper.queryByMapperSql
### The error occurred while executing a query
### SQL: select param_val from dc_system_param where param_code = ?
### Cause: java.sql.SQLException: connection disabled
; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; connection disabled; nested exception is java.sql.SQLException: connection disabled
Caused by: java.sql.SQLException: connection disabled
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure .The last packet successfully received from the server was 1,116 milliseconds ago. The last packet sent successfully to the server was 1,121 milliseconds ago.
Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
原因分析:
定位:这是数据库的原因。解决方案:
LIMIT基本语法:
SELECT * FROM table LIMIT [offset], rows | rows OFFSET [offset];
offset:行开始的行的索引。0表示从第1行 开始显示(包括第1行);1表示从第2行开始显示(包括第2行)。
rows:显示的条数,填多少,就会显示多少条。填1,表示会显示1行;填8,表示会显示8行。
解决方法:适当限制查询的条数。
SELECT * FROM orange LIMIT 0,xxxxxx;