概述
当我们在做SQL语句做条件查询,往往会使用分页插件PageHelper
,分页插件PageHelper
在条件查询中使用having做条件筛选时,会出现Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'memberName' in 'having clause'; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'memberName' in 'having clause'
这样一个异常,出现这样的原因还得从分页插件PageHelper
底层查询的一个小原理说起, 在使用Having是特别需要注意这个情况,下面根据事实情况结合分页插件PageHelper
底层查询机制做分析。
一、出现问题的情况
1. 写在Mybaitis查询数据库的SQL语句
<select id="selectOrderListByCondition" parameterType="string" resultType="hashmap">
SELECT
m.name AS memberName,
m.phoneNumber As telephone,
o.id,
o.orderDate,
o.orderType,
o.orderStatus,
s.name As setmealName
FROM
t_order o
LEFT JOIN t_member m ON o.member_id = m.id
LEFT JOIN t_setmeal s ON o.setmeal_id = s.id
<if test="value != null and value.length > 0">
HAVING memberName = #{value}
or setmealName = #{value}
or orderStatus = #{value}
or orderType = #{value}
</if>
ORDER BY memberName
</select>
2. 使用PageHelper后控制台的报错情况
### SQL: SELECT count(0) FROM t_order o LEFT JOIN t_member m ON o.member_id = m.id LEFT JOIN t_setmeal s ON o.setmeal_id = s.id HAVING memberName = ? OR setmealName = ? OR orderStatus = ? OR orderType = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'memberName' in 'having clause'
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'memberName' in 'having clause', dubbo version: 2.6.0, current host: 127.0.0.1
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'memberName' in 'having clause'
### The error may exist in com/itheima/dao/OrderDao.xml
### The error may involve com.itheima.dao.OrderDao.selectOrderListByCondition-Inline
### The error occurred while setting parameters
### SQL: SELECT count(0) FROM t_order o LEFT JOIN t_member m ON o.member_id = m.id LEFT JOIN t_setmeal s ON o.setmeal_id = s.id HAVING memberName = ? OR setmealName = ? OR orderStatus = ? OR orderType = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'memberName' in 'having clause'
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'memberName' in 'having clause'
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy39.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy49.selectOrderListByCondition(Unknown Source)
at com.itheima.service.OrderServiceImpl.pageQuery(OrderServiceImpl.java:312)
at com.itheima.service.OrderServiceImpl$$FastClassBySpringCGLIB$$d136e7c6.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:747)
3. 使用Navicat在数据库的查询情况
由下面的SQL语句查询结果来看,写的SQL是没有问题的,是能够查询到我们需要的结果。
3.1 SQL语句
SELECT
m.NAME AS memberName,
m.phoneNumber AS telephone,
o.id,
o.orderDate,
o.orderType,
o.orderStatus,
s.NAME AS setmealName
FROM
t_order o
LEFT JOIN t_member m ON o.member_id = m.id
LEFT JOIN t_setmeal s ON o.setmeal_id = s.id
HAVING
memberName = '18811679442'
OR setmealName = '18811679442'
OR orderStatus = '18811679442'
OR orderType = '18811679442'
OR telephone = '18811679442'
ORDER BY
memberName
二、出现问题原因分析
1. 分页插件PageHelper底层查询的小原理机制,需要特别注意
回头看Idea报错的异常信息,图中两处黄色线框出来的一部分需要特别注意,MyBatis框架下使用分页插件PageHelper
时,分页插件PageHelper
底层是使用了我们的SQL语句做了一个查询,找不到having
中的字段。分页插件PageHelper
做了一个count(0)
的查询, 不就是对查询结果做一个计数吗?那么,既然是计数,那having
中的字段(或起的别名字段)肯定是不存在于查询结果中的,而having
的含义本身就是对结果进行筛选。既然查询结果没有这些字段,自然而然就会包错,找不到having
中的字段。为什么分页插件PageHelper
会把我们本身要查询的SQL语句换成了查询count(0)
的SQL语句呢,其实底层是为了获取到分页Page中的total值,也就是我们查询出多少条数据的值,所以问题出现在这里。
SELECT count(0)
FROM t_order o
LEFT JOIN t_member m ON o.member_id = m.id
LEFT JOIN t_setmeal s ON o.setmeal_id = s.id
HAVING memberName = ?
OR setmealName = ?
OR orderStatus = ?
OR orderType = ?
2. 按照控制台提供的sql做一个查询实现
根据MyBatis提供的SQL做了查询实现,结论是查询字段不存在Unknown column 'memberName' in 'having clause'
,这就是分页插件PageHelper
拿到我们的SQL语句做处理时犯得一个小错误。
SELECT
count( 0 )
FROM
t_order o
LEFT JOIN t_member m ON o.member_id = m.id
LEFT JOIN t_setmeal s ON o.setmeal_id = s.id
HAVING
memberName = '18811679442'
OR setmealName = '18811679442'
OR orderStatus = '18811679442'
OR orderType = '18811679442'
OR telephone = '18811679442'
ORDER BY
memberName
三、出现问题解决方法
1. 方法一:在使用PageHelper做分页查询是SQL语句中加一个DISTINT(推荐)
-
MyBatis框架中xml的SQL语句
<select id="selectOrderListByCondition" parameterType="string" resultType="hashmap"> SELECT DISTINCT m.name AS memberName, m.phoneNumber AS telephone, o.id, o.orderDate, o.orderType, o.orderStatus, s.name AS setmealName FROM t_order o LEFT JOIN t_member m ON o.member_id = m.id LEFT JOIN t_setmeal s ON o.setmeal_id = s.id <if test = "value != null and value.length > 0" > HAVING memberName = #{value} OR setmealName = #{value} OR orderStatus = #{value} OR orderType = #{value} OR telephone = #{value} </if> ORDER BY memberName </select>
-
Idea中反馈的两条SQL
14:39:04,387 DEBUG selectOrderListByCondition_COUNT:159 - ==> Preparing: SELECT count(0) FROM (SELECT DISTINCT m.name AS memberName, m.phoneNumber AS telephone, o.id, o.orderDate, o.orderType, o.orderStatus, s.name AS setmealName FROM t_order o LEFT JOIN t_member m ON o.member_id = m.id LEFT JOIN t_setmeal s ON o.setmeal_id = s.id HAVING memberName = ? OR setmealName = ? OR orderStatus = ? OR orderType = ? OR telephone = ?) table_count 14:39:04,388 DEBUG selectOrderListByCondition_COUNT:159 - ==> Parameters: 18811679442(String), 18811679442(String), 18811679442(String), 18811679442(String), 18811679442(String) 14:39:04,389 DEBUG selectOrderListByCondition_COUNT:159 - <== Total: 1 14:39:04,391 DEBUG selectOrderListByCondition:159 - ==> Preparing: SELECT DISTINCT m.name AS memberName, m.phoneNumber AS telephone, o.id, o.orderDate, o.orderType, o.orderStatus, s.name AS setmealName FROM t_order o LEFT JOIN t_member m ON o.member_id = m.id LEFT JOIN t_setmeal s ON o.setmeal_id = s.id HAVING memberName = ? OR setmealName = ? OR orderStatus = ? OR orderType = ? OR telephone = ? ORDER BY memberName limit ?,? 14:39:04,391 DEBUG selectOrderListByCondition:159 - ==> Parameters: 18811679442(String), 18811679442(String), 18811679442(String), 18811679442(String), 18811679442(String), 0(Integer), 10(Integer)
SELECT count(0) FROM ( SELECT DISTINCT m.name AS memberName, m.phoneNumber AS telephone, o.id, o.orderDate, o.orderType, o.orderStatus, s.name AS setmealName FROM t_order o LEFT JOIN t_member m ON o.member_id = m.id LEFT JOIN t_setmeal s ON o.setmeal_id = s.id HAVING memberName = ? OR setmealName = ? OR orderStatus = ? OR orderType = ? OR telephone = ? ) table_count
SELECT DISTINCT m.name AS memberName, m.phoneNumber AS telephone, o.id, o.orderDate, o.orderType, o.orderStatus, s.name AS setmealName FROM t_order o LEFT JOIN t_member m ON o.member_id = m.id LEFT JOIN t_setmeal s ON o.setmeal_id = s.id HAVING memberName = ? OR setmealName = ? OR orderStatus = ? OR orderType = ? OR telephone = ? ORDER BY memberName limit ?,?
2. 方法三:对原有SQL做优化,但会影响性能
- 优化后Navicat查询sql和结果
SELECT * FROM ( SELECT m.NAME AS memberName, m.phoneNumber AS telephone, o.id, o.orderDate, o.orderType, o.orderStatus, s.NAME AS setmealName FROM t_order o LEFT JOIN t_member m ON o.member_id = m.id LEFT JOIN t_setmeal s ON o.setmeal_id = s.id ORDER BY memberName ) AS orderList WHERE memberName = '18811679442' OR setmealName = '18811679442' OR orderStatus = '18811679442' OR orderType = '18811679442' OR telephone = '18811679442'
- 优化后MyBatis中xml查询sql和结果
<select id="selectOrderListByCondition" parameterType="string" resultType="hashmap"> SELECT * FROM ( SELECT m.NAME AS memberName, m.phoneNumber AS telephone, o.id, o.orderDate, o.orderType, o.orderStatus, s.NAME AS setmealName FROM t_order o LEFT JOIN t_member m ON o.member_id = m.id LEFT JOIN t_setmeal s ON o.setmeal_id = s.id ORDER BY memberName ) AS orderList <if test = "value != null and value.length > 0" > WHERE memberName = #{value} OR setmealName = #{value} OR orderStatus = #{value} OR orderType = #{value} OR telephone = #{value} </if> </select>
- 查询结果展示
13:54:04,825 DEBUG SpringManagedTransaction:87 - JDBC Connection [com.mysql.jdbc.JDBC4Connection@575bcc3e] will be managed by Spring 13:54:04,825 DEBUG selectOrderListByCondition_COUNT:159 - ==> Preparing: SELECT count(0) FROM (SELECT m.NAME AS memberName, m.phoneNumber AS telephone, o.id, o.orderDate, o.orderType, o.orderStatus, s.NAME AS setmealName FROM t_order o LEFT JOIN t_member m ON o.member_id = m.id LEFT JOIN t_setmeal s ON o.setmeal_id = s.id) AS orderList WHERE memberName = ? OR setmealName = ? OR orderStatus = ? OR orderType = ? OR telephone = ? 13:54:04,825 DEBUG selectOrderListByCondition_COUNT:159 - ==> Parameters: 18811679442(String), 18811679442(String), 18811679442(String), 18811679442(String), 18811679442(String) 13:54:04,825 DEBUG selectOrderListByCondition_COUNT:159 - <== Total: 1 13:54:04,826 DEBUG selectOrderListByCondition:159 - ==> Preparing: SELECT * FROM ( SELECT m.NAME AS memberName, m.phoneNumber AS telephone, o.id, o.orderDate, o.orderType, o.orderStatus, s.NAME AS setmealName FROM t_order o LEFT JOIN t_member m ON o.member_id = m.id LEFT JOIN t_setmeal s ON o.setmeal_id = s.id ORDER BY memberName ) AS orderList WHERE memberName = ? OR setmealName = ? OR orderStatus = ? OR orderType = ? OR telephone = ? limit ?,? 13:54:04,827 DEBUG selectOrderListByCondition:159 - ==> Parameters: 18811679442(String), 18811679442(String), 18811679442(String), 18811679442(String), 18811679442(String), 0(Integer), 10(Integer) 13:54:04,828 DEBUG selectOrderListByCondition:159 - <== Total: 6 13:54:04,828 DEBUG SqlSessionUtils:186 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@276a1376]
- Mybatis框架下使用分页查询PageHelper执行的两条SQL语句分别如下:
SELECT count(0) FROM ( SELECT m.NAME AS memberName, m.phoneNumber AS telephone, o.id, o.orderDate, o.orderType, o.orderStatus, s.NAME AS setmealName FROM t_order o LEFT JOIN t_member m ON o.member_id = m.id LEFT JOIN t_setmeal s ON o.setmeal_id = s.id ) AS orderList WHERE memberName = ? OR setmealName = ? OR orderStatus = ? OR orderType = ? OR telephone = ?
SELECT * FROM ( SELECT m.NAME AS memberName, m.phoneNumber AS telephone, o.id, o.orderDate, o.orderType, o.orderStatus, s.NAME AS setmealName FROM t_order o LEFT JOIN t_member m ON o.member_id = m.id LEFT JOIN t_setmeal s ON o.setmeal_id = s.id ORDER BY memberName ) AS orderList WHERE memberName = ? OR setmealName = ? OR orderStatus = ? OR orderType = ? OR telephone = ? limit ?,?
- 查询结果展示
3. 方法四:在Java业务处理中做代码逻辑处理
在做条件查询时,会经常遇到数据流庞大,SQL语句已经没办法在做调优了,这时候我们只需要把常用的数据存到Rdeis缓存中,从缓存中拿到数据做逻辑判断取数据的查询。
- 思路一
加载页面时,从数据库拿到所有的数据,将数据存进Redis缓存中,当我们需要做条件筛选或条件查询时,直接从Redis取数据,根据查询或筛选条件通过Java逻辑判断直接取符合要求的数据。 - 思路二
把查询率比较高的数据存进Redis缓存中,每次查询先走缓存,缓存查不到再走数据库。
文章为本人原创,转载请注明出处和链接,首先感谢各位大佬阅读,文章若有不对之处,请多多指教,大家相互学习,谢谢!