一、错误描述

在mysql数据库运行this.list(Wrappers.<~>query().select(" DISTINCT usernama,update_By").lambda().orderByDesc(UserInfo::getUpdateTime)); java代码（即“SELECT  DISTINCT username, update_By FROM userinfo  ORDER BY update_time DESC”）直接报“[Err] 3065 - Expression #1 of ORDER BY clause is not in SELECT list, references column 'pms.userInfo.update_time' which is not in SELECT list; this is incompatible with DISTINCT”错误异常，详细日志如下

[SQL]SELECT  DISTINCT username, update_By FROM userInfo  @b@@b@ORDER BY update_time DESC@b@@b@[Err] 3065 - Expression #1 of ORDER BY clause is not in SELECT list, references column 'pms.userInfo.update_time' which is not in SELECT list; this is incompatible with DISTINCT@b@@b@@b@org.apache.ibatis.exceptions.PersistenceException: @b@### Error querying database.  Cause: java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list, references column 'pms.userInfo.update_time' which is not in SELECT list; this is incompatible with DISTINCT@b@### The error may exist in com/xwood/cloud/common/mapper/UserInfoMapper.java (best guess)@b@### The error may involve defaultParameterMap@b@### The error occurred while setting parameters@b@### SQL: SELECT DISTINCT username, update_By FROM userInfo WHERE del_flag = '0' AND id = ?) ORDER BY update_time DESC@b@### Cause: java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list, references column 'pms.userInfo.update_time' which is not in SELECT list; this is incompatible with DISTINCT@b@	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)@b@	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)@b@	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)@b@	at sun.reflect.GeneratedMethodAccessor257.invoke(Unknown Source)@b@	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)@b@	at java.lang.reflect.Method.invoke(Method.java:498)@b@	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)@b@	at com.sun.proxy.$Proxy213.selectList(Unknown Source)@b@	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)@b@	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:177)@b@	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78)@b@	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96)@b@	at com.sun.proxy.$Proxy259.selectList(Unknown Source)@b@	at sun.reflect.GeneratedMethodAccessor493.invoke(Unknown Source)@b@	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)@b@	at java.lang.reflect.Method.invoke(Method.java:498)@b@	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)@b@	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)@b@	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)@b@	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)@b@	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)@b@	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)@b@	at com.sun.proxy.$Proxy260.selectList(Unknown Source)@b@	at com.baomidou.mybatisplus.extension.service.impl.ServiceImpl.list(ServiceImpl.java:284)@b@	at com.xwood.afs.cloud.channel.workflow.service.impl.WorkflowTaskInfoServiceImpl.getApproveUsers(WorkflowTaskInfoServiceImpl.java:56)@b@	at com.xwood.afs.cloud.channel.workflow.service.impl.WorkflowTaskInfoServiceImpl$$FastClassBySpringCGLIB$$60036d66.invoke(<generated>)@b@	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)@b@	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)@b@	at @b@	...@b@	at sun.reflect.GeneratedMethodAccessor495.invoke(Unknown Source)@b@	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)@b@	at java.lang.reflect.Method.invoke(Method.java:498)@b@	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197)@b@	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141)@b@	at com.xwood.afs.cloud.common.core.tangram.enditpoint.TangramInvocableHandlerMethod.invokeAndHandle(TangramInvocableHandlerMethod.java:79)@b@	at com.xwood.afs.cloud.common.core.tangram.enditpoint.TangramMethodHandlerAdapter.invokeHandlerMethod(TangramMethodHandlerAdapter.java:829)@b@	at com.xwood.afs.cloud.common.core.tangram.enditpoint.TangramMethodHandlerAdapter.handleInternal(TangramMethodHandlerAdapter.java:743)@b@	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)@b@	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1060)@b@	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:962)@b@	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)@b@	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)@b@	at javax.servlet.http.HttpServlet.service(HttpServlet.java:665)@b@	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)@b@	at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)@b@	at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:327)@b@	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:115)@b@	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:81)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119)@b@	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:105)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:149)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at com.xwood.afs.cloud.common.core.security.component.AfsUserInfoInjectFilter.doFilterInternal(AfsUserInfoInjectFilter.java:65)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.oauth2.server.resource.web.BearerTokenAuthenticationFilter.doFilterInternal(BearerTokenAuthenticationFilter.java:129)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:103)@b@	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:89)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90)@b@	at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:110)@b@	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:80)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:55)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)@b@	at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:211)@b@	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:183)@b@	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358)@b@	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271)@b@	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)@b@	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)@b@	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)@b@	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:93)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)@b@	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)@b@	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)@b@	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)@b@	at com.xwood.afs.cloud.common.core.holder.RequestHeaderFilter.doFilter(RequestHeaderFilter.java:72)@b@	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)@b@	at com.xwood.afs.cloud.common.core.security.filter.IgnoreUriFilter.doFilter(IgnoreUriFilter.java:48)@b@	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)@b@	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)@b@	at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)@b@	at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)@b@	at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68)@b@	at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)@b@	at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68)@b@	at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:117)@b@	at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)@b@	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)@b@	at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)@b@	at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)@b@	at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)@b@	at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)@b@	at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)@b@	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)@b@	at io.undertow.servlet.handlers.SendErrorPageHandler.handleRequest(SendErrorPageHandler.java:52)@b@	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)@b@	at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:269)@b@	at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:78)@b@	at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:133)@b@	at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:130)@b@	at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)@b@	at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)@b@	at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:249)@b@	at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:78)@b@	at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:99)@b@	at io.undertow.server.Connectors.executeRootHandler(Connectors.java:387)@b@	at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:841)@b@	at org.apache.skywalking.apm.plugin.undertow.v2x.SWRunnable.run(SWRunnable.java:45)@b@	at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)@b@	at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:2019)@b@	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1558)@b@	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1449)@b@	at java.lang.Thread.run(Thread.java:748)@b@Caused by: java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list, references column 'csrop_channel.userInfo.update_time' which is not in SELECT list; this is incompatible with DISTINCT@b@	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)@b@	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)@b@	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)@b@	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:974)@b@	at com.mysql.cj.jdbc.ClientPreparedStatement.execute$original$wyJb7wQK(ClientPreparedStatement.java:391)@b@	at com.mysql.cj.jdbc.ClientPreparedStatement.execute$original$wyJb7wQK$accessor$inLvklam(ClientPreparedStatement.java)@b@	at com.mysql.cj.jdbc.ClientPreparedStatement$auxiliary$VUcUYfzH.call(Unknown Source)@b@	at org.apache.skywalking.apm.agent.core.plugin.interceptor.enhance.InstMethodsInter.intercept(InstMethodsInter.java:86)@b@	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java)@b@	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409)@b@	at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:627)@b@	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)@b@	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)@b@	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)@b@	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)@b@	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)@b@	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)@b@	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)@b@	at sun.reflect.GeneratedMethodAccessor252.invoke(Unknown Source)@b@	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)@b@	at java.lang.reflect.Method.invoke(Method.java:498)@b@	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)@b@	at com.sun.proxy.$Proxy277.query(Unknown Source)@b@	at sun.reflect.GeneratedMethodAccessor252.invoke(Unknown Source)@b@	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)@b@	at java.lang.reflect.Method.invoke(Method.java:498)@b@	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)@b@	at com.sun.proxy.$Proxy277.query(Unknown Source)@b@	at sun.reflect.GeneratedMethodAccessor252.invoke(Unknown Source)@b@	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)@b@	at java.lang.reflect.Method.invoke(Method.java:498)@b@	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)@b@	at com.sun.proxy.$Proxy277.query(Unknown Source)@b@	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:67)@b@	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)@b@	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)@b@	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:136)@b@	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)@b@	... 143 common frames omitted

二、解决方法

1. 查询mysql数据库版本，显示

select version();

结果：8.0.25

2. 查询sql_mode

select @@global.sql_mode

结果：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

3. 方法一：命令去除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';@b@@b@但该方法在重启Mysql服务后会失效，重启服务后会失效

4. 方法二：修改mysql的配置文件关闭ONLY_FULL_GROUP_BY SQL模式

sudo vim /etc/mysql/conf.d/mysql.cnf@b@@b@文件底部追加:@b@@b@[mysqld]@b@sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION@b@@b@ @b@@b@保存并重启mysql@b@@b@sudo service mysql restart

5. 方法三；去除代码的DISTINCT的函数

this.list(Wrappers.<~>query().select(" DISTINCT usernama,update_By").lambda().orderByDesc(UserInfo::getUpdateTime));

  修改为

this.list(Wrappers.<~>query().select(" usernama,update_By").lambda().orderByDesc(UserInfo::getUpdateTime));