错误内容:
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 ‘,times,sequence,cte_user,ute_user,cte_time,ute_time FROM tb_sdc_plot_param’ at line 1

情景

今天在调试后端一个接口的时候出现了一个非常诡异的问题:
空白标签页

后端报错内容:

2020-10-19 14:07:44.663 ERROR 16076 --- [nio-8891-exec-8] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is 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 'times,sequence,cte_user,ute_user,cte_time,ute_time  FROM tb_sdc_plot_param' at line 1
### The error may exist in com/xxx/mapper/TbSdcPlotParamMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT id,name,code,app_key,sync_method,param_type,value_type,compute_sign,interval,times,sequence,cte_user,ute_user,cte_time,ute_time  FROM tb_sdc_plot_param
### 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 'times,sequence,cte_user,ute_user,cte_time,ute_time  FROM tb_sdc_plot_param' at line 1
; 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 'times,sequence,cte_user,ute_user,cte_time,ute_time  FROM tb_sdc_plot_param' at line 1] with root cause

分析

一看这个错误内容com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax;, 我他喵直接呵呵,这不就是SQL语句写错了吗,这我知道太常见了!可是比较诡异的地方来了:
首先这是一个SpringCloud项目查询接口使用的是@RestController一路下来@GetMapping调用业务层,之后业务逻辑直接使用Spring+tkMybatis与持久层交互,根本不涉及SQL语句的编写?那么问题来了我根本就没有写sql,sql自己出错难了难不成我发现了tkMybatis的错误?! NO不太可能,于是我开debug,将自动编写的sql语句复制到Navcat查询:
SpringBoot项目SQL语句中出现数据库关键字导致:You have an error in your SQL syntax； check the manual that corresponds-小白菜博客
嗯一模一样的错误, 排除后端代码的问题,然后我突然发现了一个奇怪的高亮单词interval,这引起了我的注意,为什么你会highlight呢,我他喵直接百度:
SpringBoot项目SQL语句中出现数据库关键字导致:You have an error in your SQL syntax； check the manual that corresponds-小白菜博客
到这里明白了,sql的变量当中出现了Mysql函数关键词interval, 但是坑的是拿关键词建表的时候为什么没有报错啊,修改这个关键词为其他,即可解决.

总结

我这里的这个错误,很有可能与大部分人出现的原因不同,有可能你只是Mysql语句写错了,例如末尾的时候多加了一个,,导致该报错,如果你与我相同使用了封装JDBC的框架例如tkMybatis等等,出现该错误,就该思考是不是类似的问题,最后interval==>inter_val,并修改PO与数据库对应,
查询成功:
成功