懒咪学编程


mysql从5.6升级到5.7后出现 Expression #1 of ORDER BY clause is not in SELECT list,this is incompatible with

时间:2020-09-09 02:32:06  来源:  作者:  所属栏目:MySQL

这里将告诉您mysql从5.6升级到5.7后出现 Expression #1 of ORDER BY clause is not in SELECT list,this is incompatible with DISTINCT,具体完成步骤:【问题】mysql从5.6

这里将告诉您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 moreCaused 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 -pEnter password: Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 5905Server 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 -pEnter password: Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 39Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.

【解决】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_modeSELECT @@GLOBAL.sql_mode;查询当前会话sql_modeSELECT @@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

mysql从5.6升级到5.7后出现 Expression #1 of ORDER BY clause is not in SELECT list,this is incompatible with DISTINCT就为您介绍到这里,感谢您关注懒咪学编程c.lanmit.com.

本文地址:https://c.lanmit.com/shujuku/MySQL/119856.html

 

推荐MySQL排行......