mysql从5.6升级到5.7后出现 Expression #1 of ORDER BY clause is not in SELECT list,this is incompatible with DISTINCT
【问题】
mysql从5.6升级到5.7后出现：插入数据和修改数据时出错
caused by: com.ibatis.common.jdbc.exception.nestedsqlexception:
--- the error occurred while applying a parameter map.
--- check the findorderlist-inlineparametermap.
--- check the statement (query failed).
--- cause: java.sql.sqlexception: expression #1 of order by clause is not in select list, references column 'ddfei.t2.add_time' which is not in select list; this is incompatible with distinct
at com.ibatis.sqlmap.engine.mapping.statement.mappedstatement.executequerywithcallback(mappedstatement.java:201)
at com.ibatis.sqlmap.engine.mapping.statement.mappedstatement.executequeryforlist(mappedstatement.java:139)
at com.ibatis.sqlmap.engine.impl.sqlmapexecutordelegate.queryforlist(sqlmapexecutordelegate.java:567)
at com.ibatis.sqlmap.engine.impl.sqlmapexecutordelegate.queryforlist(sqlmapexecutordelegate.java:541)
at com.ibatis.sqlmap.engine.impl.sqlmapsessionimpl.queryforlist(sqlmapsessionimpl.java:118)
at org.springframework.orm.ibatis.sqlmapclienttemplate$3.doinsqlmapclient(sqlmapclienttemplate.java:295)
at org.springframework.orm.ibatis.sqlmapclienttemplate$3.doinsqlmapclient(sqlmapclienttemplate.java:1)
at org.springframework.orm.ibatis.sqlmapclienttemplate.execute(sqlmapclienttemplate.java:200)
... 43 more
caused by: java.sql.sqlexception: expression #1 of order by clause is not in select list, references column 'ddfei.t2.add_time' which is not in select list; this is incompatible with distinct
at com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:964)
at com.mysql.jdbc.mysqlio.checkerrorpacket(mysqlio.java:3970)
at com.mysql.jdbc.mysqlio.checkerrorpacket(mysqlio.java:3906)
at com.mysql.jdbc.mysqlio.sendcommand(mysqlio.java:2524)
at com.mysql.jdbc.mysqlio.sqlquerydirect(mysqlio.java:2677)
at com.mysql.jdbc.connectionimpl.execsql(connectionimpl.java:2549)
at com.mysql.jdbc.preparedstatement.executeinternal(preparedstatement.java:1861)
at com.mysql.jdbc.preparedstatement.execute(preparedstatement.java:1192)
at com.alibaba.druid.filter.filterchainimpl.preparedstatement_execute(filterchainimpl.java:2931)
at com.alibaba.druid.wall.wallfilter.preparedstatement_execute(wallfilter.java:588)
at com.alibaba.druid.filter.filterchainimpl.preparedstatement_execute(filterchainimpl.java:2929)
at com.alibaba.druid.filter.filtereventadapter.preparedstatement_execute(filtereventadapter.java:440)
at com.alibaba.druid.filter.filterchainimpl.preparedstatement_execute(filterchainimpl.java:2929)
at com.alibaba.druid.filter.filtereventadapter.preparedstatement_execute(filtereventadapter.java:440)
at com.alibaba.druid.filter.filterchainimpl.preparedstatement_execute(filterchainimpl.java:2929)
at com.alibaba.druid.proxy.jdbc.preparedstatementproxyimpl.execute(preparedstatementproxyimpl.java:118)
at com.alibaba.druid.pool.druidpooledpreparedstatement.execute(druidpooledpreparedstatement.java:493)
at com.ibatis.sqlmap.engine.execution.sqlexecutor.executequery(sqlexecutor.java:185)
at com.nbtv.orm.dao.ibatis.executor.limitsqlexecutor.executequery(limitsqlexecutor.java:57)
at com.ibatis.sqlmap.engine.mapping.statement.mappedstatement.sqlexecutequery(mappedstatement.java:221)
at com.ibatis.sqlmap.engine.mapping.statement.mappedstatement.executequerywithcallback(mappedstatement.java:189)
... 50 more
【场景】
老库
root@<ddfei-mysq01|~>:#mysql -uroot -p
enter password:
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 5905
server version: 5.6.40-log mysql community server (gpl)
mysql> show variables like '%sql_mode%';
+---------------+--------------------------------------------+
| variable_name | value |
+---------------+--------------------------------------------+
| sql_mode | strict_trans_tables,no_engine_substitution |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)
新库
[root@ddfei-mysql01 ~]# mysql -uroot -p
enter password:
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 39
server version: 5.7.28 source distribution
copyright (c) 2000, 2019, oracle and/or its affiliates. all rights reserved.
oracle is a registered trademark of oracle corporation and/or its
affiliates. other names may be trademarks of their respective
owners.
【解决】
mysql> select @@global.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> set @@global.sql_mode='';
query ok, 0 rows affected, 1 warning (0.00 sec)
mysql> set @@global.sql_mode = 'strict_trans_tables,no_engine_substitution';
query ok, 0 rows affected, 2 warnings (0.00 sec)
mysql> select @@global.sql_mode;
+--------------------------------------------+
| @@global.sql_mode |
+--------------------------------------------+
| strict_trans_tables,no_engine_substitution |
+--------------------------------------------+
1 row in set (0.00 sec)
这样只是暂时修改，永久生效可以改配置文件my.cnf 然后 service mysqld restart 生效
【原因】
可能是
1、在sql查询语句中不需要group by的字段上使用any_value()函数
这种对于已经开发了不少功能的项目不太合适，毕竟要把原来的sql都给修改一遍
2、distinct和order by都会对数据进行排序操作，所以会产生冲突
在sql语句中使用distinct时不使用order by进行排序，获取结果集后通过php进行数据的排序，同时也提高了mysql的性能。同时group by，limit和其中的一起搭配使用也会导致错误。
mysql5.7版本中，如果distinct和order by一起使用将会报3065错误，sql语句无法执行。这是由于5.7版本语法比之前版本语法要求更加严格导致的。
3、
mysql server 默认开启了 sql_mode=only_full_group_by 模式，此模式要求 group by 字段必须出现在查询项中（select），否则就会报出该错误。因为group by处理变得更加复杂，包括检测功能依赖性。
【补充】
查询sql_mode的方式
查询全局sql_mode
select @@global.sql_mode;
查询当前会话sql_mode
select @@session.sql_mode;
...
【参考】
https://www.cnblogs.com/liukaifeng/p/10103810.html
官方翻译说明
mysql5.6升级到5.7后 linux下修改mysql的sql_mode模式
https://blog.csdn.net/xu1988923/article/details/89310458
转自：高效码农：https://www.xugj520.cn/archives/68.html