如果这个现象是个bug,请问最早在哪个版本解决了呢? druid版本:1.0.26 数据库版本:mysql5.X 项目框架:springmvc+spring+Hibernate 下面是我的sql语句: select * from users order by convert(display_name using 'gbk') desc 有正常的结果输出:
莫小贝 吕秀才 郭芙蓉 阿强 H阿伟 hello world A啊牛
错误信息如下: 2017-09-06 19:03:41.186:[ERROR] method:com.alibaba.druid.filter.stat.StatFilter.mergeSql(StatFilter.java:147) merge sql error, dbType mysql, sql : select * from users order by convert(display_name using 'gbk') desc com.alibaba.druid.sql.parser.ParserException: syntax error at com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser.primaryRest(MySqlExprParser.java:430) at com.alibaba.druid.sql.parser.SQLExprParser.primary(SQLExprParser.java:590) at com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser.primary(MySqlExprParser.java:167) at com.alibaba.druid.sql.parser.SQLExprParser.expr(SQLExprParser.java:94) at com.alibaba.druid.sql.parser.SQLExprParser.parseSelectOrderByItem(SQLExprParser.java:1116) at com.alibaba.druid.sql.parser.SQLExprParser.parseOrderBy(SQLExprParser.java:1100) at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.query(MySqlSelectParser.java:164) at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:62) at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseSelect(MySqlStatementParser.java:237) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:183) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:148) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:143) at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:54) at com.alibaba.druid.filter.stat.StatFilter.mergeSql(StatFilter.java:145) at com.alibaba.druid.filter.stat.StatFilter.createSqlStat(StatFilter.java:630) at com.alibaba.druid.filter.stat.StatFilter.statementPrepareAfter(StatFilter.java:305) at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:124) at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448) at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:342) at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:331) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:161) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:159) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1859) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1836) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1816) at org.hibernate.loader.Loader.doQuery(Loader.java:900) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342) at org.hibernate.loader.Loader.doList(Loader.java:2526) at org.hibernate.loader.Loader.doList(Loader.java:2512) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2342) at org.hibernate.loader.Loader.list(Loader.java:2337) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338) at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1833) at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:231) at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:157) at com.thundersoft.mdm.dao.impl.UserDaoImpl.getAllUsers(UserDaoImpl.java:73) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at com.alibaba.druid.support.spring.stat.DruidStatInterceptor.invoke(DruidStatInterceptor.java:72) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) at com.sun.proxy.$Proxy268.getAllUsers(Unknown Source) at com.thundersoft.mdm.web.controller.TestOrderByController.testOrderBy(TestOrderByController.java:23) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) at javax.servlet.http.HttpServlet.service(HttpServlet.java:620) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.springframework.orm.hibernate4.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:151) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at com.thundersoft.mdm.utils.MapSessionTransfer.doFilter(MapSessionTransfer.java:19) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at com.thundersoft.mdm.utils.AuthFilter.doFilter(AuthFilter.java:221) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at com.thundersoft.mdm.utils.SystemContextFilter.doFilter(SystemContextFilter.java:34) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at com.thundersoft.mdm.utils.GzipFilter.doFilter(GzipFilter.java:42) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at com.thundersoft.mdm.utils.HttpDeleteAndPutFromContentFilter.doFilterInternal(HttpDeleteAndPutFromContentFilter.java:87) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:123) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:745)
原提问者GitHub用户syoukaihou
根据错误信息,可以看出你的SQL语句中使用了convert(display_name using 'gbk')
表达式,而MySQL不支持在ORDER BY子句中直接使用该表达式。因此,你需要进行一些修改来解决这个问题。
首先,你可以尝试移除convert()
函数,并直接使用display_name
字段进行排序:
SELECT * FROM users ORDER BY display_name DESC
如果你需要按照GBK编码进行排序,可以使用COLLATE
子句指定排序规则为utf8_general_ci
:
SELECT * FROM users ORDER BY display_name COLLATE utf8_general_ci DESC
请注意,COLLATE
子句只能用于字符类型的列,所以你需要确保display_name
是一个字符类型的列。
这个错误信息比较简略,需要查看详细的错误日志或者代码才能确定具体的原因。不过,根据错误信息中提到的"merge sql error"和"dbType mysql",可以初步判断是在使用MySQL数据库时执行了错误的MERGE语句。
MySQL数据库并不支持MERGE语句,因此执行该语句会报错。如果需要实现类似MERGE的功能,可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE语句,或者使用以下两个语句的组合:
lasso
Copy
INSERT INTO ... SELECT ... FROM ... WHERE NOT EXISTS ...
UPDATE ... SET ... WHERE EXISTS ...
其中,INSERT INTO ... ON DUPLICATE KEY UPDATE语句的作用是,插入一条新记录或者更新一条已有记录;而第二种语句组合的作用是,先进行一次SELECT查询,判断记录是否存在,如果不存在则进行一次INSERT操作,如果存在则进行一次UPDATE操作。
把select * from users order by convert(display_name using 'gbk') desc修改成 select * from users order by convert(display_name using gbk) desc即可解决上面的异常。
原回答者GitHub用户syoukaihou
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。