mysql 分页查询出现重复数据问题排查
mysql 分页查询出现重复数据
问题分析定位
问题解决方案

mysql 分页查询出现重复数据
2019的最后一天，才发现今年快过去了，就想着写点东西纪念一下，而且正好昨天项目上遇见一个让我感到很奇怪的问题，一个简单的分页查询居然出现问题了，让人难以置信啊，但是问题就是出现了。所以现在就将我对这件事的问题排查过程做一个简单的记录。希望能帮助到大家。顺便方便自己做一个遇到问题的记录吧！
问题分析定位

在测试提供的测试用例以后，我测试了一下，在查第二页和第三页的数据里面确实有重复的数据，这真的很神奇。

1.我的第一反应就是分页插件有问题，所以将每次请求的sql语句打印出来看一下，发现没啥问题啊。这就很尴尬啊。

2.然后就不用分页，将分页干掉，然后查询出来整体数据，发现没啥问题，那就表明出现问题的地方在分页上面。但是问题有出来了，我大mysql的分页语句limit会有问题么？

3.问题出现的地方找到了，发现问题，那就要去解决。然后经过查询资料和百度了一下，发现这个问题是我们 order by 语句与limit分页语句组合在一起出现了问题

问题解决方案

因为我们在使用order by 字段时，如果这个排序字段会有相同的值，这个时候与limit组合以后就会出现数据重复现象。那这是为啥呢？经过了解，这个问题是在mysql5.6以后才会出现，mysql5.5不会出现。这是由于mysql5.6做了优化，将排序方法改成了堆排序，堆排序每次请求的结果都会不一致。
这个问题我的解决方式如下：

原来的语句：
	select * from pd_a order by xgsj limit 10
修改以后的语句：
	select * from pd_a order by xgsj,id limit 10

在回出现重复的排序字段后面在加上一个不会出现重复的字段做一个联合排序。这样就可以解决重复数据问题。


结束语

2019年马上就要过去了，祝愿大家2020年心想事成。

首先我说明一下我遇到的情况,以便正在阅读本文的你可以决定是否需要继续看下去:
假设我有一张表,里面有4个字段,分别叫做ABCD,主键是A,对应的我有一个pojo和一个hbm文件,pojo没什么,hbm文件里的<id>标签对应的却是B(也许你说怎么会有这种情况,其实实际情况是我在一个视图里遇到的这个问题,那里可不只有4个字段,我这么写是希望你能最快的理解).
好的,现在问题来了.....我使用了B作为条件查询了这张表,姑且我们定义这个条件为B=1,我的表里符合这个条件的数据有2条,当然它们的A字段的值是不同的(一个A=1,一个A=2),查询的结果是:两条一模一样的数据(也就是两条都是A=1或者A=2).


前面我描述了问题的现象,如果你遇到的和我一样,你可以继续看下去,或许你已经在我上面的描述里看出了问题所在.是的,就是因为hbm里的主键设置,由于<id>设置成了不唯一字段,而Hibernate认为它是唯一的,导致了查询出的两条数据有相同的B字段的值时,Hibernate直接复用了第一条查出的数据,因为它认为第二条既然主键都相同了,那其它的也一定一样.


原因既然解释了,解决方案就是:
1.找出你这个实体真正的唯一字段,将其标记为<id>(切记将<id>这个标签放在hbm文件其他<property>之前,否则...你肯定会这么做的)
2.使用联合主键,这个我没试,网上例子也很多,搜索一下Hibernate联合主键,你就有答案了.


好了,如果本文对你有些帮助,就在下面回复一下,让我知道我写的东西没有白费.或者你有什么问题,也可以留下.
拒做伸手党,从我做起!

QueryDsl与SpringDataJPA实现分页级联查询出现重复数据
1.需求
2.界面
3.业务代码
4.重复数据问题
5.总结

1.需求
项目中资源交换任务实现多表级联查询。

两表一对多关系，需要多字段查询。
2.界面

3.业务代码
QueryDsl提供了对JPA，SQL，Collection，MongoDB等不同类型查询的实现。

这里讲解和SpringDataJPA集成实现多表分页级联查询。
@Override
    public Page<DirExchangeCfgDetailDto> searchDetailByPage(Pageable pageable, MultiValueMap<String, String> parameters) {
        if (pageable.getSort().isUnsorted()) {
            pageable = PageRequest.of(pageable.getPageNumber(), pageable.getPageSize(), QSort.by(qCfgEntity.createDate.desc(),qCfgDetailEntity.id.desc()));
        }
        Page<Tuple> tuplePage = repository.findAll(null, pageable, jpqlQuery -> jpqlQuery.from(qCfgEntity).innerJoin(qCfgDetailEntity)
                .on(qCfgDetailEntity.configId.eq(qCfgEntity.id)).where(builderConditions(parameters)), qCfgDetailEntity,qCfgEntity);

        return tuplePage.map(tuple -> {
            DirExchangeCfgDetailDto cfgDetailDto =  dtoMapper.map(tuple.get(qCfgDetailEntity), DirExchangeCfgDetailDto.class);
            // 判断是否为默认任务
            if (DEFAULT_EXCHANGE_CFG_CODE.equalsIgnoreCase(tuple.get(qCfgEntity).getCfgCode())) {
                cfgDetailDto.setIsDefault(true);
            }else {
                cfgDetailDto.setIsDefault(false);
            }
            cfgDetailDto.setCfgName(tuple.get(qCfgEntity).getCfgName());
            cfgDetailDto.setFlowDirection(tuple.get(qCfgEntity).getFlowDirection());
            cfgDetailDto.setChangeType(tuple.get(qCfgEntity).getChangeType());
            cfgDetailDto.setCronStr(tuple.get(qCfgEntity).getCronStr());
            return cfgDetailDto;
        });
    }

private BooleanBuilder builderConditions(MultiValueMap<String, String> parameters) {
        final String resourceName = parameters.getFirst("resourceName");
        final String availableStatus = parameters.getFirst("availableStatus");
        final String flowDirection = parameters.getFirst("flowDirection");
        final String changeType = parameters.getFirst("changeType");
        BooleanBuilder builder = new BooleanBuilder();
        if (StringUtils.isNotEmpty(resourceName)) {
            builder.and(qCfgDetailEntity.resourceName.eq(resourceName));
        }
        if (StringUtils.isNotEmpty(availableStatus)) {
            builder.and(qCfgDetailEntity.availableStatus.eq(availableStatus));
        }
        if (StringUtils.isNotEmpty(flowDirection)) {
            builder.and(qCfgEntity.flowDirection.eq(flowDirection));
        }
        if (StringUtils.isNotEmpty(changeType)) {
            builder.and(qCfgEntity.changeType.eq(changeType));
        }
        return builder;
    }

主要调用JPQLQuery的方法，将结果存放到一个Tuple的多元组中.相比原生sql,简单清晰了很多。

多表投影查询示例：



把集合封装成Page返回。
4.重复数据问题
当设置交换任务为主表(一对多表中的多项表)进行多表映射查询时，会出现重复的返回数据。



发现问题出现在QueryDsl执行编译好的sql语句，看到sql语句关联变成了cross join交叉查询，无论是left join还是其他是同样情况。



解决方法需要设交换方案表（一对多表中的单项对应表）为主表，交换位置。



编译sql语句正常。


5.总结
当遇到复杂、投影、动态查询时我们可以考虑使用QueryDSL做开发。以上方案可以解决大多数持久层开发问题。在使用SpringDataJPA和QueryDSL实现多表关联级联查询时，注意将一对多单项关联表设为主表进行查询。

先放一条 重复数据 的脏SQL
SELECT O.ID AS ID,O.NAME AS NAME,COUNT(C.ID) AS CLASS_NUM FROM T_BASE_ORGANIZATION O
LEFT JOIN T_XSXX_CLASSINFORMATION C ON O.ID = C.FACULTY_ID
LEFT JOIN T_SZDW_CLASSMEETING_PLAN P ON C.ID = P.CLASS_ID
WHERE C.GRADE = 2010
GROUP BY O.ID,O.NAME,O.ORDERS
ORDER BY O.ORDERS

这表结构是：

T_BASE_ORGANIZATION 学院表

T_XSXX_CLASSINFORMATION 班级表

T_SZDW_CLASSMEETING_PLAN 班会表

一个学院 多个班级

一个班级 多个班会

总之引发查询出重复数据就是因为，表之间关联的关系不是一对一的而是一对多，从而导致数据重复。

解决方式
方法一：在 GROUP BY 加上（多表的外键） C.ID
SELECT O.ID AS ID,O.NAME AS NAME,COUNT(C.ID) AS CLASS_NUM FROM T_BASE_ORGANIZATION O
LEFT JOIN T_XSXX_CLASSINFORMATION C ON O.ID = C.FACULTY_ID
LEFT JOIN T_SZDW_CLASSMEETING_PLAN P ON C.ID = P.CLASS_ID
WHERE C.GRADE = 2010
GROUP BY O.ID,O.NAME,O.ORDERS,C.ID
ORDER BY O.ORDERS

    

方法二：在返回结果数据的 地方加上DISTINCT函数, COUNT(DISTINCT(C.ID))
SELECT O.ID AS ID,O.NAME AS NAME,COUNT(DISTINCT(C.ID)) AS CLASS_NUM FROM T_BASE_ORGANIZATION O
LEFT JOIN T_XSXX_CLASSINFORMATION C ON O.ID = C.FACULTY_ID
LEFT JOIN T_SZDW_CLASSMEETING_PLAN P ON C.ID = P.CLASS_ID
WHERE C.GRADE = 2010
GROUP BY O.ID,O.NAME,O.ORDERS
ORDER BY O.ORDERS



  



方法三：在做left join、right join、inner join的时候将 关联的表先DISTINCT(外键) 子查询一遍    
SELECT O.ID AS ID,O.NAME AS NAME,COUNT(C.ID) AS CLASS_NUM FROM T_BASE_ORGANIZATION O
LEFT JOIN T_XSXX_CLASSINFORMATION C ON O.ID = C.FACULTY_ID
LEFT JOIN (SELECT DISTINCT(Z.CLASS_ID) FROM T_SZDW_CLASSMEETING_PLAN Z ) P ON C.ID = P.CLASS_ID
WHERE C.GRADE = 2010
GROUP BY O.ID,O.NAME,O.ORDERS
ORDER BY O.ORDERS

不推荐 使用方法一

ORDER BY  函数 影响范围广。

推荐使用方法一

子查询 作用范围小，不影响整体SQL。