SQLServer中手动更改排序规则导致MyBatis分页查询报错

解决方案
,
340 words , 1 mins.

报错信息

在 MyBatis 中写了一个常规的查询，本地运行的时候一直报错。分页查询的时候会先执行一个 count 查询，日志里发现是在分页查询的时候本应该去掉的 order by 部分也被拼接到了 count 语句中，导致 SQL 语法出现问题报错。

MyBatis 中的代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<sql id="baseQuery">
    SELECT
        t.request_no,
        t.created_by,
        t.created_date,
        t.updated_by,
        t.updated_date,
        u1.user_name created_user_name,
        u2.user_name updated_user_name
    FROM admin_procurement t
    LEFT JOIN comm_sys_user u1 ON t.created_by collate Chinese_PRC_CI_AS = u1.um_id
    LEFT JOIN comm_sys_user u2 ON t.updated_by collate Chinese_PRC_CI_AS = u2.um_id
    WHERE 1 = 1
</sql>
1
2
3
4
<select id="loadAllProcurement" resultType="com.techmask.bl.core.data.model.DataModel">
    <include refid="baseQuery" />
    ORDER BY t.created_date DESC
</select>

报错信息如下：

1
2
3
4
5
6
7
### Error querying database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
### The error may exist in file [...\build\resources\main\mappers\sqlserver\Repository.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select count(0) from (SELECT            t.request_no,             t.created_by,             t.created_date,             t.updated_by,             t.updated_date,             u1.user_name created_user_name,             u2.user_name updated_user_name         FROM admin_procurement t         LEFT JOIN comm_sys_user u1 ON t.created_by collate Chinese_PRC_CI_AS = u1.um_id         LEFT JOIN comm_sys_user u2 ON t.updated_by collate Chinese_PRC_CI_AS = u2.um_id         WHERE 1 = 1                                                                                                                                             ORDER BY t.created_date DESC) tmp_count
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
; uncategorized SQLException; SQL state [S0001]; error code [1033]; 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

原本这个查询是正常的，在其他模块也是这样的写法。按照 AI 的解决方案加了同名 _COUNT 查询 也没效果。

原因

后面回忆起来因为本地数据库的默认排序规则和测试环境不一致，重新建表的时候因为排序规则不一致查询报错所以在查询中手动指定了排序规则 collate Chinese_PRC_CI_AS，把这个查询中指定的排序规则删掉，修改了字段的排序规则就不报错了。

根本原因是 PageHelper 分页插件在遇到复杂查询的时候，会导致 SQL 改写失败或者查询结果不符合预期。

下面是一些常见的会被认为是复杂查询的场景：

  1. 多语句查询（一个 Mapper 方法中包含多条 SQL 语句）
  2. 包含复杂子查询（尤其是嵌套在 SELECT/FROM 后的多层子查询）