环境
1.sql server 数据库
2.使用mybatis-plus分页插件
需求
有两种表分别是电脑表、电脑配套表。两张表的关系是一对多。表数据如下:
电脑表
电脑配套表
需求是查询那些电脑拥有特殊配套。
正常查询
image.png
过滤只返回电脑信息
image.png
mapper xml的代码
<mapper namespace="com.xxxx.xxxx.business.dao.ComputerDao">
<select id="getList"
resultType="com.xxxx.xxxx.business.entity.Computer" >
select DISTINCT c.* from computer c LEFT join computer_part cp on c.id = cp.computer_id
where cp.type ='特殊配套'
</select>
</mapper>
swagger调用返回结果
swagger
很简单的功能可以看到total是2,但是数据内容是4。分别有一条重复。
原因在于上面的正常查询,确实是4条数据。但是mapper里面明明已经配置了distinct为什么还会重复呢?
因为使用了mybatis-plus分页插件,所以实际查询语句是被起包装过的,具体,通过控制台可以找到实际sql执行语句。如下:
image.png
WITH selectTemp AS (SELECT DISTINCT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __row_number__, c.* from computer c LEFT join computer_part cp on c.id = cp.computer_id where cp.type ='特殊配套') SELECT * FROM selectTemp WHERE __row_number__ BETWEEN 1 AND 10 ORDER BY __row_number__
sql语句拷贝到数据库连接工具执行:
sql执行结果
确实是四条。
到此真相大白,确实是插件的锅。查看插件源码:
public String buildPaginationSql(String originalSql, long offset, long limit) {
StringBuilder pagingBuilder = new StringBuilder();
String orderby = getOrderByPart(originalSql);
String distinctStr = "";
String loweredString = originalSql.toLowerCase();
String sqlPartString = originalSql;
if (loweredString.trim().startsWith("select")) {
int index = 6;
if (loweredString.startsWith("select distinct")) {
distinctStr = "DISTINCT ";
index = 15;
}
sqlPartString = originalSql.substring(index);
}
pagingBuilder.append(sqlPartString);
if (StringUtils.isEmpty(orderby)) {
orderby = "ORDER BY CURRENT_TIMESTAMP";
}
StringBuilder sql = new StringBuilder();
sql.append("WITH selectTemp AS (SELECT ").append(distinctStr).append("TOP 100 PERCENT ").append(" ROW_NUMBER() OVER (").append(orderby).append(") as __row_number__, ").append(pagingBuilder).append(") SELECT * FROM selectTemp WHERE __row_number__ BETWEEN ").append(offset + 1L).append(" AND ").append(offset + limit).append(" ORDER BY __row_number__");
return sql.toString();
}
可以看到它确实是把我自带的distinct给提出去,再跟ROW_NUMBER函数拼接,导致去重失败。也可以看到源码中select distinct要是不只间隔一个空格,它还识别不出来。经过尝试多个空格还是导致报错。到此,基本锅已定。我这个版本是3.0.3。github上最新的是3.4.0。源码如下
public DialectModel buildPaginationSql(String originalSql, long offset, long limit) {
StringBuilder pagingBuilder = new StringBuilder();
String orderby = getOrderByPart(originalSql);
String distinctStr = StringPool.EMPTY;
String loweredString = originalSql.toLowerCase();
String sqlPartString = originalSql;
if (loweredString.trim().startsWith("select")) {
int index = 6;
if (loweredString.startsWith("select distinct")) {
distinctStr = "DISTINCT ";
index = 15;
}
sqlPartString = sqlPartString.substring(index);
}
pagingBuilder.append(sqlPartString);
// if no ORDER BY is specified use fake ORDER BY field to avoid errors
if (StringUtils.isBlank(orderby)) {
orderby = "ORDER BY CURRENT_TIMESTAMP";
}
long firstParam = offset + 1;
long secondParam = offset + limit;
String sql = "WITH selectTemp AS (SELECT " + distinctStr + "TOP 100 PERCENT " +
" ROW_NUMBER() OVER (" + orderby + ") as __row_number__, " + pagingBuilder +
") SELECT * FROM selectTemp WHERE __row_number__ BETWEEN " +
//FIX#299:原因:mysql中limit 10(offset,size) 是从第10开始(不包含10),;而这里用的BETWEEN是两边都包含,所以改为offset+1
firstParam + " AND " + secondParam + " ORDER BY __row_number__";
return new DialectModel(sql);
}
一看终究还是错付了。实际尝试还是会存在同样的问题。
我暂时没想通为啥作者要先处理 select 或者select distinct,而不是直接把原始sql整段保留,然后在拼接ROW_NUMBER函数进行分页处理。
解决
1.等待作者处理
2.手动分页
3.子查询的方式,避免连表查询
最终处理方案3,修改mapper代码如下:
<mapper namespace="com.xxxx.xxxx.business.dao.ComputerDao">
<select id="getList"
resultType="com.xxxx.xxxx.business.entity.Computer" >
select * from computer c
where id in (select computer_id from computer_part where type = '特殊配套')
</select>
</mapper>
swagger执行结果
期望的结果
结语
本文到此结束。
偶尔分享一点,记得点赞加关注。
我也是mybatis-plus的小白使用者,本文如有不正确之处,望各种留情。
网友评论