mysql - mybatis搜索数据第一次只返回1行，第二次返回所有行

Question

我用springBoot + mybatis搜索mysql数据，发现有些搜索只返回1行，但是如果我再用同样的条件搜索，就返回所有行。以下是主要代码：

public void doBusiness(WorkerEntity workerEntity) {
    
        Date accountTime = DateUtils.stringToDate(workerEntity.getAccountantTime(), DateStyle.YYYY_MM_DD);
        String tableSuffix = DateUtils.yyyyMM(accountTime);


        long minId = workerEntity.getStartId();
        long maxId = workerEntity.getEndId();
        int totalCount = 0;

        // define beginId and endId
        long beginId;
        long endId;
        for ( ; minId <= maxId; ) {
            beginId = minId;
            endId = Math.min(beginId + 599, maxId);
            minId = endId + 1;

            PaymentProceedsDto proceedsDto = new PaymentProceedsDto();
            proceedsDto.setAccountantTime(accountTime);
            // other condition
            ....
            proceedsDto.setStartBatchId(beginId);
            proceedsDto.setEndBatchId(endId);
            updatePaymentService.searchAndUpdate(proceedsDto, workerEntity, tableSuffix);
        }
        ...
    }

@Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRES_NEW)
    public void searchAndUpdate(PaymentProceedsDto proceedsDto, WorkerEntity workerEntity, String tableSuffix) {
        List<Long> proceedsIdList = paymentProceedsService.querySumProceedsUnAccountList(proceedsDto);
        log.info("fetch sum proceeds unAccount ids end first, batchId: {}, start: {}, end: {}, size: {}",
                workerEntity.getRequestBatchCode(), proceedsDto.getStartBatchId(), proceedsDto.getEndBatchId(),
                proceedsIdList.size());
        
        if (proceedsIdList.size() > 0 && proceedsIdList.size() < 10) {
            List<Long> proceedsIdList2 = paymentProceedsService.querySumProceedsUnAccountList(proceedsDto);
            log.info("fetch sum proceeds unAccount ids end second, batchId: {}, start: {}, end: {}, size: {}",
                    workerEntity.getRequestBatchCode(), proceedsDto.getStartBatchId(), proceedsDto.getEndBatchId(),
                    proceedsIdList2.size());
          
        }
      ...
    }

映射器：

<select id="querySumProceedsUnAccountList" resultType="java.lang.Long">
    select id
    from <include refid="table_name"/>
    where id between #{proceedsDto.startBatchId} and #{proceedsDto.endBatchId}
    and <include refid="querySumProceedsUnAccountCondition"/>
  </select>
  <sql id="querySumProceedsUnAccountCondition">
    accountant_time = #{proceedsDto.accountantTime}
    and enter_accounts_state = #{proceedsDto.enterAccountsState}
    and enter_ce_state = #{proceedsDto.enterCeState}
    and ebs_summary_state = #{proceedsDto.ebsSummaryState}
    and is_del = 0
  </sql>

如果我的循环批量大小是 599，也像 1 到 599 之间的 sql id。它需要返回 600 大小，但第一次搜索只返回 1 行，同样条件第二次搜索返回 600 行。

我也尝试将批量大小设置为 1000，第一次搜索也只返回 1 行，第二次搜索返回 1001 行。

这是第一个搜索日志：

[xxx] [DEBUG] [2021-09-03 15:58:07.532] [o.a.i.l.j.BaseJdbcLogger:debug:137] [pool-6-thread-1] ==>  Preparing: select id from 
payment_proceeds_current where id between ? and ? and accountant_time = ? and enter_accounts_state = ? and enter_ce_state = ? and ebs_summary_state = ? and
 is_del = 0[TID:N/A]
[xxx] [DEBUG] [2021-09-03 15:58:07.532] [o.a.i.l.j.BaseJdbcLogger:debug:137] [pool-6-thread-1] ==> Parameters: 134744124(Long)
, 134744723(Long), 2021-05-31 00:00:00.0(Timestamp), 1(Integer), 2(Integer), 2(Integer)[TID:552267.108.16306558875320157]
[xxx] [DEBUG] [2021-09-03 15:58:07.534] [o.a.i.l.j.BaseJdbcLogger:debug:137] [pool-6-thread-1] <==      Total: 1[TID:552267.10
8.16306558875320157]
[xxx] [INFO ] [2021-09-03 15:58:07.535] [c.b.r.r.e.i.UpdatePaymentServiceImpl:searchAndUpdate:44] [pool-6-thread-1] fetch sum 
proceeds unAccount ids end first, batchId: 20210901225304381, start: 134744124, end: 134744723, size: 1[TID:N/A]

第二次搜索日志：

[xxx] [DEBUG] [2021-09-03 15:58:07.535] [o.a.i.l.j.BaseJdbcLogger:debug:137] [pool-6-thread-1] ==>  Preparing: select id from 
payment_proceeds_current where id between ? and ? and accountant_time = ? and enter_accounts_state = ? and enter_ce_state = ? and ebs_summary_state = ? and
 is_del = 0[TID:N/A]
[xxx] [DEBUG] [2021-09-03 15:58:07.536] [o.a.i.l.j.BaseJdbcLogger:debug:137] [pool-6-thread-1] ==> Parameters: 134744124(Long)
, 134744723(Long), 2021-05-31 00:00:00.0(Timestamp), 1(Integer), 2(Integer), 2(Integer)[TID:552267.108.16306558875360159]
[xxx] [DEBUG] [2021-09-03 15:58:07.538] [o.a.i.l.j.BaseJdbcLogger:debug:137] [pool-6-thread-1] <==      Total: 600[TID:552267.
108.16306558875360159]
[xxx] [INFO ] [2021-09-03 15:58:07.539] [c.b.r.r.e.i.UpdatePaymentServiceImpl:searchAndUpdate:49] [pool-6-thread-1] fetch sum 
proceeds unAccount ids end second, batchId: 20210901225304381, start: 134744124, end: 134744723, size: 600[TID:N/A]

mybatis local-cache-scope 为语句，cache-enabled 为false。

mybatis:
  mapper-locations:
    - classpath*:mapper/**/*Mapper.xml
  configuration:
    cache-enabled: false
    local-cache-scope: statement

我也尝试将mybatis版本从3.4.5升级到3.5.6，但是不行。

有人知道如何解决这个问题，谢谢。

标签： mysqlmybatis