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

一、问题

在使用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语句时直接拼接在了后面，这就导致了这个分号出错了。。。。

最后去掉分号问题解决。

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