项目切换多租户导致的数据库SQL执行异常

先贴异常日志

java.lang.reflect.InvocationTargetException 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 com.xxl.job.core.handler.impl.MethodJobHandler.execute$original$IQ6MQqKI(MethodJobHandler.java:29) 
at com.xxl.job.core.handler.impl.MethodJobHandler.execute$original$IQ6MQqKI$accessor$I1xs3DyD(MethodJobHandler.java) 
at com.xxl.job.core.handler.impl.MethodJobHandler$auxiliary$W3gs9Xn9.call(Unknown Source) 
at org.apache.skywalking.apm.agent.core.plugin.interceptor.enhance.InstMethodsInter.intercept(InstMethodsInter.java:86) 
at com.xxl.job.core.handler.impl.MethodJobHandler.execute(MethodJobHandler.java) 
at com.xxl.job.core.thread.JobThread.run(JobThread.java:152) Caused by: org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: 
Failed to process, Error SQL: 
select * from sc_test 
where error_count > 1 # 失败次数>1  
and status = 0 # 状态正常
and is_deleted = 0 
order by create_time desc 
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440) 
at com.sun.proxy.$Proxy205.selectList(Unknown Source) 
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223) 
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:166) 
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77) 
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148) 
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89) 
at com.sun.proxy.$Proxy327.listExceptionTest(Unknown Source) 
at com.baomidou.mybatisplus.core.toolkit.ExceptionUtils.mpe(ExceptionUtils.java:39) 
at com.baomidou.mybatisplus.extension.parser.JsqlParserSupport.parserSingle(JsqlParserSupport.java:52) 
at com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor.beforeQuery(TenantLineInnerInterceptor.java:65) 
at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:78) 
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) at com.sun.proxy.$Proxy287.query(Unknown Source) 
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:111) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) 
at com.sun.proxy.$Proxy287.query(Unknown Source) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ... 25 more Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "#" at line 3, column 17. Was expecting one of: ";" "CONNECT" "EMIT" "GROUP" "HAVING" "PIVOT" "START" "WINDOW" 
at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:31468) 
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:31301) 
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:163) 
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:188) 
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:63) 
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:38) 
at com.baomidou.mybatisplus.extension.parser.JsqlParserSupport.parserSingle(JsqlParserSupport.java:49) ... 33 more

---

上面这个报错日志对应的异常SQL是：

select * from sc_test 
where error_count > 1 # 失败次数>1  
and status = 0 # 状态正常
and is_deleted = 0 
order by create_time desc 

首先可以确认，这个SQL之前一直是可以正常运行的，只是这次上了多租户才被影响到；

---

上面的异常日志重点是：

Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: “#” at line 3, column 17. Was expecting one of: “;” “CONNECT” “EMIT” “GROUP” “HAVING” “PIVOT” “START” “WINDOW”

大致可以看出来，问题出现在 # ，因为多租户是通过mybatis拦截器拦截SQL、解析SQL并且自动拼接租户ID。

解析SQL时发现 # 解析失败；

---

解决方案，SQL修改为：

select * from sc_test 
<!-- 失败次数>1 -->
where error_count > 1
<!-- 状态正常 -->
and status = 0 
and is_deleted = 0 
order by create_time desc 

修改为SQL解析器支持的注释格式就可以了！！！ 项目中最好不要使用 # 注释SQL，避免某些解析器解析失败。