mysql异常java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list

最近运维同学升级了数据库后，我们的程序很多接口都报错了一查看是数据库脚本执行报错

Caused by: java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list, references column 'm_tenant_ucx_use_detail_month.lev_code' which is not in SELECT list; this is incompatible with DISTINCT
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3461)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:4

网上查了一下这个是因为数据库开启了only_full_group_by规则校验，执行命令关闭校验就恢复了

 set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
 set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

查看命令：

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

执行命令如果不生效的话可能需要重启mysql数据库或者重启服务试试

service mysqld start;