MyBatis-Plus mysql命令行或navicat中可以执行的sql，在mybatis-plus中报异常：Failed to process, please exclude the tableName or statementId

当前使用版本(必填,否则不予处理)

mybatis-plus 3.4.0

该问题是如何引起的？(确定最新版也有问题再提!!!)

1.最新版本存在此问题。 2.当执行一个查询sql，此查询sql是实现分组排序取第n行，此sql的select中含有字符”@num := 0”时，mybatis-plus无法正确执行，直接抛出异常。但是在mysql命令行或navicat中可以正常执行的此sql。 3.具体SQL如下： SELECT group_id, -- IF(@grps!=aa.group_id,@num:=1,@num:=@num+1) as total, (IF(TRUE, 1, 2)) AS custom_type FROM (SELECT (@num := 0) ,(@grps :=- 1) from dual) cc, ( SELECT concat(1, "", 2) AS group_id -- concat(data_id, "", parent_id) AS group_id FROM dual -- data_relation ) AS aa ORDER BY group_id

重现步骤(如果有就写完整)

1.建立一个继承自com.baomidou.mybatisplus.core.mapper的mapper类 2.编写一个方法query 3.建立此mapper的对应xml，并在对应的sql查询方法中： SELECT group_id, -- IF(@grps!=aa.group_id,@num:=1,@num:=@num+1) as total, (IF(TRUE, 1, 2)) AS custom_type FROM (SELECT (@num := 0) ,(@grps :=- 1) from dual) cc, ( SELECT concat(1, "", 2) AS group_id -- concat(data_id, "", parent_id) AS group_id FROM dual -- data_relation ) AS aa ORDER BY group_id

报错信息

Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.

Error SQL: SELECT group_id, -- IF(@grps### The error occurred while executing a query !=aa.group_id,@num:=1,@num:=@num+1) as total, (IF(TRUE, 1, 2)) AS custom_type FROM (SELECT (@num := 0) ,(@grps :=- 1) from dual) cc, ( SELECT concat(data_id, "", parent_id) AS group_id FROM data_relation ) AS aa ORDER BY group_id at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) 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:498) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ... 144 common frames omitted Caused by: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId. Error SQL: SELECT group_id, -- IF(@grps!=aa.group_id,@num:=1,@num:=@num+1) as total, (IF(TRUE, 1, 2)) AS custom_type FROM (SELECT (@num := 0) ,(@grps :=- 1) from dual) cc, ( SELECT concat(data_id, "", parent_id) AS group_id FROM data_relation ) AS aa ORDER BY group_id at com.baomidou.mybatisplus.core.toolkit.ExceptionUtils.mpe(ExceptionUtils.java:39) at com.baomidou.mybatisplus.core.parser.AbstractJsqlParser.parser(AbstractJsqlParser.java:76) at com.baomidou.mybatisplus.extension.handlers.AbstractSqlParserHandler.sqlParser(AbstractSqlParserHandler.java:76) at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.intercept(PaginationInterceptor.java:173) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) at com.sun.proxy.$Proxy324.prepare(Unknown Source) 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:498) at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) at com.pct.dotware.commons.data.datascope.DataScopeInterceptor.intercept(DataScopeInterceptor.java:69) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) at com.sun.proxy.$Proxy324.prepare(Unknown Source) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:94) at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:68) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:165) at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:92) 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:498) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) at com.sun.proxy.$Proxy323.query(Unknown Source) 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:498) at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) at com.pct.dotware.commons.data.interceptor.AppendCurrUserInterceptor.intercept(AppendCurrUserInterceptor.java:116) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) at com.sun.proxy.$Proxy323.query(Unknown Source) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ... 150 common frames omitted Caused by: net.sf.jsqlparser.JSQLParserException: null at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:151) at com.baomidou.mybatisplus.core.parser.AbstractJsqlParser.parser(AbstractJsqlParser.java:62) ... 183 common frames omitted Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: ":" ":" at line 6, column 16.

Was expecting one of:

"&"
")"
"."
"::"
"<<"
">>"
"COLLATE"
"["
"^"
"|"

at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:25031)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:24875)
at net.sf.jsqlparser.parser.CCJSqlParser.PrimaryExpression(CCJSqlParser.java:10473)
at net.sf.jsqlparser.parser.CCJSqlParser.BitwiseXor(CCJSqlParser.java:10151)
at net.sf.jsqlparser.parser.CCJSqlParser.MultiplicativeExpression(CCJSqlParser.java:10104)
at net.sf.jsqlparser.parser.CCJSqlParser.AdditiveExpression(CCJSqlParser.java:10067)
at net.sf.jsqlparser.parser.CCJSqlParser.BitwiseAndOr(CCJSqlParser.java:10013)
at net.sf.jsqlparser.parser.CCJSqlParser.ConcatExpression(CCJSqlParser.java:9989)
at net.sf.jsqlparser.parser.CCJSqlParser.SimpleExpression(CCJSqlParser.java:9982)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectExpressionItem(CCJSqlParser.java:4566)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectItem(CCJSqlParser.java:4781)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectItemsList(CCJSqlParser.java:4543)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:4041)
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:4264)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:3923)
at net.sf.jsqlparser.parser.CCJSqlParser.SubSelect(CCJSqlParser.java:12580)
at net.sf.jsqlparser.parser.CCJSqlParser.FromItem(CCJSqlParser.java:5840)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:4055)
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:4264)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:3923)
at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:3916)
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:130)
at net.sf.jsqlparser.parser.CCJSqlParser.Statements(CCJSqlParser.java:498)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:149)
... 184 common frames omitted

Comment From: qmdx

分页？ JsqlParser 无法智能分析你的 sql 请查看文档排除该 执行 方法自动 sql 优化

Comment From: chenmyGitHub

排除指定的语句自动sql优化， 怎么指定呢