Mybatis-plus| You have an error in your SQL syntax the right syntax to use near ‘LIMIT 0,10‘

最新推荐文章于 2023-05-25 21:55:59 发布

KevinBrain

最新推荐文章于 2023-05-25 21:55:59 发布

阅读量1.5k

点赞数 3

CC 4.0 BY-SA版权

分类专栏：疑难杂症特效处方文章标签： mybatis mysql java bug 数据库

本文链接：https://blog.youkuaiyun.com/Cobbyer/article/details/115221388

疑难杂症特效处方专栏收录该内容

15 篇文章

订阅专栏

在使用Mybatis-Plus自定义SQL查询博客时，遇到分页插件导致的SQL语法错误。问题在于Mapper方法中的分号多余，移除后解决了查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、问题

在使用Mybatis-plus自定义SQL语句进行查询时，需要用到分页插件进行分页，Mapper层的方法如下：

@Select("select id, title, type_id,user_id, description,update_time,first_picture,views,comment_count  from t_blog where update_time BETWEEN #{start_date} and #{end_date} ORDER BY update_time DESC;")
    @Results(
            {
                    @Result(id=true, column = "id", property = "id"),
                    @Result(column ="appreciation", property = "appreciation"),
                    @Result(column = "commentabled", property = "commentabled"),
//                    @Result(column = "content", property = "content"),
                    @Result(column = "update_time", property = "updateTime"),
                    @Result(column = "description", property = "description"),
                    @Result(column = "first_picture", property = "firstPicture"),
                    @Result(column = "share_statement", property = "shareStatement"),
                    @Result(column = "title", property = "title"),
                    @Result(column = "views", property = "views"),
                    @Result(column = "comment_count", property = "commentCount"),
                    @Result(column = "type_id", property = "type", one=@One(select = "com.kevin.mapper.TypeMapper.selectTypeById", fetchType = FetchType.EAGER)),
                    @Result(column = "user_id", property = "user", one=@One(select = "com.kevin.mapper.UserMapper.selectUserById", fetchType = FetchType.EAGER))
            }
    )
    List<Blog> selectBlogByYearItem( IPage page, @Param("start_date") Date start_date, @Param("end_date") Date end_date);

报错如下：You have an error in your SQL syntax; check the manual that corresponds to your MySQL

### 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 'LIMIT 0,10' 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 'LIMIT 0,10' at line 1] with root cause

经过各种百度方法测试无效。。

二、解决

当我去掉分页插件，只是用日期参数时能够正常获取数据结果。最后去顶问题出在分页插件上，仔细研究报错信息发现如下：

### SQL: select id, title, type_id,user_id, description,update_time,first_picture,views,comment_count  from t_blog where update_time BETWEEN ? and ? ORDER BY update_time DESC; LIMIT ?,?

问题所在：报错信息中显示出了Mybatis-plus生成的SQL语言，问题就出在多了个分号！！！ ==》ORDER BY update_time DESC; LIMIT ?,?

最后检查代码发现Mapper方法中的：

@Select("select id, title, type_id,user_id, description,update_time,first_picture,views,comment_count  from t_blog where update_time BETWEEN #{start_date} and #{end_date} ORDER BY update_time DESC;")

末尾加了个分号，于是乎Mybatis在生成SQL语句时直接拼接在了后面，这就导致了这个分号出错了。。。。

最后去掉分号问题解决。

最后丝袜哥表示很赞同并给我传回了正确的结果！