同样的sql语句在mybatis里报错，在数据库中不报错

Question

问题遇到的现象和发生背景

我想通过下面的sql语句查到 和fy_model_icon关联的fy_model_manual表的最新的一条数据，然后下面的sql直接在数据库里是跑的通的，但是在xml文件里就报错，这是为啥呀，还是说用其他的写法

问题相关代码，请勿粘贴截图

SELECT 
            <include refid="fyModelIconColumns"/>
            ,b.name as "producer.name"
            ,mm.id AS "manual.id",
            mm.model_id AS "manual.modelId",
            mm.name AS "manual.name",
            mm.state AS "manual.state",
            mm.m_url AS "manual.mUrl",
            mm.c_url AS "manual.cUrl",
            mm.create_date AS "manual.createDate"
        FROM fy_model_icon a 
        LEFT JOIN fy_producer b ON a.producer_id = b.id
        LEFT JOIN ( 
            select 
                id,
                model_id,
                name,
                state,
                m_url,
                c_url 
            from fy_model_manual  where state = '1'
            order by create_date desc 
            limit 1
         ) AS mm ON mm.model_id = a.id 

运行结果及报错内容

org.springframework.jdbc.BadSqlGrammarException:

Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 29

The error may involve defaultParameterMap

The error occurred while setting parameters

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 29

; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 29
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:399)
at com.sun.proxy.$Proxy40.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:205)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
at com.sun.proxy.$Proxy196.listIconWithManual(Unknown Source)
at com.fyts.modules.admin.service.FyModelIconService.pageList(FyModelIconService.java:68)
at com.fyts.modules.admin.service.FyModelIconService$$FastClassBySpringCGLIB$$e731a5b4.invoke()
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:718)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)
at com.fyts.modules.admin.service.FyModelIconService$$EnhancerBySpringCGLIB$$376facf5.pageList()
at com.fyts.modules.admin.service.FyModelIconService$$FastClassBySpringCGLIB$$e731a5b4.invoke()
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:718)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)
at com.fyts.modules.admin.service.FyModelIconService$$EnhancerBySpringCGLIB$$14ef43eb.pageList()
at com.fyts.modules.api.web.ManualCodeController.findManualPage(ManualCodeController.java:67)
at sun.reflect.GeneratedMethodAccessor396.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)

我的解答思路和尝试过的方法

我想要达到的结果

Answer 1

我感觉你这个排序有点问题order by create_date desc，如果按照这个进行排序的话数据也会出现随机性，因为表中的时间日期有可能是相同的，建议再加一个条件排序，比如说加上id,order by create_date desc,id desc