spring boot sqlserver PaginationInterceptor分页异常：Mybatis-plus 分页功能报错：除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图

记一次开发过程中Mybatis Plus PaginationInterceptor分页功能异常，找了很多博客，都没有分析为什么导致异常，大多数是自定义count查询，这样要多写很多代码，本篇文章对异常进行了源码分析，定位到了问题所在，有用请点赞支持！！！

先说明结论

当数据源是SQLServer & 使用PaginationInterceptor分页插件 & 使用order by时三种情况并存的情况，如果SQL中出现with(nolock)，会导致SQL无法被分页插件解析，从而出现在执行count查询时报错。

异常如下：

com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

异常复现：

PaginationInterceptor存在分页异常bug：当我们数据源是SQLServer时，因为SQLServer在增删改操作时是表锁，SQLServer会阻止脏读，导致查询效率变低，所以通常我们写SQL查询的时候会加上with(nolock)，允许查询语句脏读，从而提升查询效率，正常写SQL是没有问题的，但是在使用PaginationInterceptor分页插件分页查询 & 使用order by时，如下：

select mx.ddbh, mx.ddxh, dd.khbh, mx.dhsl as xsl

, cast(mx.wbdj * mx.hjhl / mx.usd_hjhl as numeric(12, 2)) as myxsdj

, cast(mx.wbje * mx.hjhl / mx.usd_hjhl as numeric(12, 2)) as myxsje

, mx.lrl, mx.lrlnew, '美元' as bzmc, mx.qfsj

, case when month(mx.qfsj) >= 7 then (year(mx.qfsj) + 1) else year(mx.qfsj) end as ddjyj

from ddmxb as mx with(nolock)

left join xsddb as dd with(nolock) on dd.ddbh = mx.ddbh

where mx.qfsj > '2019-07-01' and mx.qfbj = 1

order by dd.ddbh desc

会报如下异常：

2023-03-13 09:07:35.735 ERROR 11728 [1084764685762138112] druid.sql.Statement : {conn-10001, pstmt-20001} execute error. SELECT COUNT(1) FROM ( select mx.ddbh, mx.ddxh, dd.khbh, mx.dhsl as xsl

, cast(mx.wbdj * mx.hjhl / mx.usd_hjhl as numeric(12, 2)) as myxsdj

, cast(mx.wbje * mx.hjhl / mx.usd_hjhl as numeric(12, 2)) as myxsje

, mx.lrl, mx.lrlnew, '美元' as bzmc, mx.qfsj

, case when month(mx.qfsj) >= 7 then (year(mx.qfsj) + 1) else year(mx.qfsj) end as ddjyj

from ddmxb as mx with(nolock)

left join xsddb as dd with(nolock) on dd.ddbh = mx.ddbh

where mx.qfsj > '2019-07-01' and mx.qfbj = 1

order by dd.ddbh desc ) TOTAL

com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:600)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7225)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3053)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:444)

at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3240)

at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)

at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3237)

at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)

at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3237)

at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:181)

at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

......

可以看到在执行count查询的时候就已经报错，执行的SQL如下：

SELECT COUNT(1)

FROM (

SELECT mx.ddbh, mx.ddxh, dd.khbh, mx.dhsl as xsl , cast(mx.wbdj * mx.hjhl / mx.usd_hjhl as numeric(12, 2)) as myxsdj , cast(mx.wbje * mx.hjhl / mx.usd_hjhl as numeric(12, 2)) as myxsje , mx.lrl, mx.lrlnew, '美元' as bzmc, mx.qfsj , case when month(mx.qfsj) >= 7 then (year(mx.qfsj) + 1) else year(mx.qfsj) end as ddjyj

FROM ddmxb as mx with(nolock)

LEFT JOIN xsddb as dd with(nolock) ON dd.ddbh = mx.ddbh

WHERE mx.qfsj > '2019-07-01' and mx.qfbj = 1 order by dd.ddbh desc ) TOTAL;

这种语法在SQLServer中执行是不通过的，可以看到其SQL并不是select count(1) from … order by格式，而是把原来的SQL整体括了起来，正常的应该是如下：

SELECT COUNT(1)

FROM ddmxb as mx with(nolock)

LEFT JOIN xsddb as dd with(nolock) ON dd.ddbh = mx.ddbh

WHERE mx.qfsj > '2019-07-01' and mx.qfbj = 1;

异常排查：

所以就需要排查为什么没有解析并替换掉原SQL，这里分析过程就不再给了，篇幅太长，直接给出定位问题的位置和结果： 首先问题肯定出现在PaginationInterceptor分页插件中，我通过对分页插件打断点一行行排查，在PaginationInterceptor类下的intercept()方法下的SqlParserUtils.getOptimizeCountSql()方法下的COUNT_SQL_PARSER.parser()方法发现SQL解析结果就是以上的错误SQL：

然后进入到parser实现方法，找到JsqlParserCountOptimize类下的parser()方法，发现在执行(Select) CCJSqlParserUtil.parse(sql)时报异常，被try catch捕获，然后在catch中提示直接使用原SQL

所以可以确定问题就是出在了这里，既然无法解析原SQL，就说明原SQL有不能被解析的单词，然后我去掉了一些SQL代码后不断重试，最终发现在去掉with(nolock)后，SQL解析正常。所以问题可以确定就是：在使用SQLServer数据源的时候，with(nolock)会导致SQL解析异常，count()查询解析结果会是一个错误的SQL。

解决方法：

原SQL中不要出现with(nolock)（不建议使用：表锁的情况下会导致查询等待，使效率变低）使用PageHelper分页插件，弃用PaginationInterceptor，（可以使用：分页插件其实就是起到一个解析并拼接SQL的作用，哪个好用就用哪个）PageHelper + PaginationInterceptor（推荐：在SQLServer数据源时使用PageHelper插件，其他数据源使用PaginationInterceptor插件，因为PaginationInterceptor可以少写一行代码）

总结：

当数据源是SQLServer & 使用PaginationInterceptor分页插件 & 使用order by时三种情况并存的情况，如果SQL中出现with(nolock)，会导致SQL无法被分页插件解析，从而出现在执行count查询时报错。

好文推荐