当前位置：首页 >数据库 > MyBatis-plus分页出现两个limit？

MyBatis-plus分页出现两个limit？

1、问题出路

测试环境、线上环境日志陆续出现此类问题，本地环境并没有出现此类异常，而且服务器中也不是持续报错，而是时而报错，时而正常，报错如下：

 2021-12-01 12:03:02.909 ERROR 28056 --- [nio-8000-exec-1] 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 'LIMIT 20' at line 6### The error may exist in file [G:\Git\xf\service-xf\target\classes\com\lm\anga\platform\anslip\dao\xml\AnSlip.map.xml]### The error may involve com.lm.anga.platform.anslip.dao.mapper.AnSlipMapper.getLastData-Inline### The error occurred while setting parameters### SQL: SELECT id_,station_id_,collect_time_,an_slip_  FROM dt_an_slip_dataWHEREstation_id_=? order by collect_time_ desc limit 1 LIMIT ?### 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 20' at line 6; 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 20' at line 6] with root 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 20' at line 6	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_40]	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_40]	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_40]	at java.lang.reflect.Constructor.newInstance(Constructor.java:422) ~[na:1.8.0_40]	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.21.jar:na]

SQL语句

 SELECT id_,station_id_,collect_time_,an_slip_  FROM dt_an_slip_dataWHEREstation_id_=? order by collect_time_ desc limit 1 LIMIT ?

原因

PageHelper 方法使用了静态的 ThreadLocal 参数，分页参数和线程是绑定的。

只要你可以保证在 PageHelper 方法调用后紧跟 MyBatis 查询方法，这就是安全的。因为 PageHelper 在 finally 代码段中自动清除了 ThreadLocal 存储的对象。

线程中start的page 不能保证线程在当前执行退出时清理完page变量

PageBean pageBean=queryFilter.getPageBean();PageHelper.startPage(pageBean.getCurrentPage(),pageBean.getPageSize());

解决

使用PageHelper插件后，清楚缓存

	PageHelper.clearPage();

建议

MyBatis-plus分页插件和PageHelper插件二选一

@Beanpublic PaginationInterceptor paginationInterceptor() {PaginationInterceptor paginationInterceptor = new PaginationInterceptor();// 开启 PageHelper 的支持paginationInterceptor.setLocalPage(true);//如果使用MyBatis-plus分页插件时，可设置为falseList<ISqlParser> sqlParserList = new ArrayList<>();paginationInterceptor.setSqlParserList(sqlParserList);retu paginationInterceptor;}