Expression #1 of ORDER BY clause is not in SELECT list
- 1、mysql查询异常:
- 2、解决办法:
- 2.1、查看是否开启了only_full_group_by规则校验
- 2.2、关闭only_full_group_by的规则校验
- 2.3、重启mysql服务器
1、mysql查询异常:
Error querying database. Cause: java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list, references column ‘xxx-数据库.T1.sort’ which is not in SELECT list; this is incompatible with DISTINCT
2、解决办法:
2.1、查看是否开启了only_full_group_by规则校验
SELECT @@GLOBAL.sql_mode;
查询结果:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SELECT @@SESSION.sql_mode;
查询结果:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
2.2、关闭only_full_group_by的规则校验
单引号中的内容是第一步查询的结果去掉“ONLY_FULL_GROUP_BY,”之后的值
set @@GLOBAL.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set @@SESSION.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
2.3、重启mysql服务器
service mysqld start;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)