Mybatis中使用PageHelper出现：除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

在使用PageHelper插件时，如果执行的sql语句中包含了order by 这个关键字，执行这句sql的时候肯定会报错：

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

这是因为order by以后返回的结果时游标，PageHelper 在执行count查询的时候就会报错。针对于这个问题PageHelper官方给出了以下的解决方法：

增加 countSuffix count 查询后缀配置参数，该参数是针对 PageInterceptor 配置的，默认值为 _COUNT。

分页插件会优先通过当前查询的 msId + countSuffix 查找手写的分页查询。

如果存在就使用手写的 count 查询，如果不存在，仍然使用之前的方式自动创建 count 查询。

例如，如果存在下面两个查询：

<select id="selectLeftjoin" resultType="com.github.pagehelper.model.Country">
    select a.id,b.countryname,a.countrycode from country a
    left join country b on a.id = b.id
    order by a.id
</select>
<select id="selectLeftjoin_COUNT" resultType="Long">
    select count(distinct a.id) from country a
    left join country b on a.id = b.id
</select>
上面的 countSuffix 使用的默认值 _COUNT，分页插件会自动获取到 selectLeftjoin_COUNT 查询，这个查询需要自己保证结果数正确。

返回值的类型必须是resultType="Long"，入参使用的和 selectLeftjoin 查询相同的参数，所以在 SQL 中要按照 selectLeftjoin 的入参来使用。

因为 selectLeftjoin_COUNT 方法是自动调用的，所以不需要在接口提供相应的方法，如果需要单独调用，也可以提供。

上面方法执行输出的部分日志如下：

DEBUG [main] - ==>  Preparing: select count(distinct a.id) from country a left join country b on a.id = b.id 
DEBUG [main] - ==> Parameters: 
TRACE [main] - <==    Columns: C1
TRACE [main] - <==        Row: 183
DEBUG [main] - <==      Total: 1
DEBUG [main] - Cache Hit Ratio [com.github.pagehelper.mapper.CountryMapper]: 0.0
DEBUG [main] - ==>  Preparing: select a.id,b.countryname,a.countrycode from country a left join country b on a.id = b.id order by a.id LIMIT 10 
DEBUG [main] - ==> Parameters: 
TRACE [main] - <==    Columns: ID, COUNTRYNAME, COUNTRYCODE
TRACE [main] - <==        Row: 1, Angola, AO
TRACE [main] - <==        Row: 2, Afghanistan, AF
TRACE [main] - <==        Row: 3, Albania, AL

 

 

自定义count语句，不在使用PageHelper的默认生成的count语句，在mybait项目中可以用如下方法解决：

<select id ="test" resultType="User">
select * from t_user order by userId
</select>


<select id ="test_COUNT" resultType="Long">
select count(0) from t_user
</select>

如果id=test的这个语句报错，新增一个id=test_COUNT的查询，入参和test一样，但是返回值必须是Long，新增的这个查询不需要声明Mapper接口，PageHelper会自动执行。

 

参考https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/Changelog.md#504---2017-08-01