更新自增ID表范围内数据

只适用于表的主键ID为自增的表数据。

场景：大数据情况下，根据筛选条件及每次更新固定条数据，A表需要获取B表中user_id 和自己user_id 相同的user_name。

首先根据自己的筛选条件查询到对应的数据范围

<select id="selectIdRange" resultType="java.lang.Long">
        SELECT
          id
        from
          (
            SELECT
              a.id,
              @rank:= @rank + 1 AS rank_no
            FROM
              (
                SELECT
                  t.id
                FROM
                  test_table t
                where
                    stat_datetime between DATE_FORMAT(#{startDate,jdbcType=VARCHAR},'%Y-%m-%d 00:00:00') and DATE_FORMAT(#{startDate,jdbcType=VARCHAR},'%Y-%m-%d 23:59:59')
                <if test="userId!=null and userId!=''">
                    and user_id =#{userId}
                </if>
                ORDER BY
                  id
              ) a,
              (
                SELECT
                  @rank:= 0
              ) b
          ) c
        where
          c.rank_no % 10000 = 0 or c.rank_no=1
    </select>

首先根据待更新ID范围更新数据

private void update(String startDate, String userId) {
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        // 查询待更新数据ID范围
        List<Long> idRange = ****DataMapper.selectIdRange(startDate, userId);
        if (CollectionUtils.isEmpty(idRange)) {
            log.info("暂无要补充数据!");
            return;
        }
        Long maxId = 0L;
        Long minId;
        for (int i = 0; i < idRange.size() + 1; i++) {
            minId = maxId;
            if (i == idRange.size()) {
                maxId = null;
            } else {
                maxId = idRange.get(i);
            }
            if (i == 0) {
                continue;
            }
            ****DataMapper.updateData(startDate, userId, maxId, minId);
        }
        stopWatch.stop();
        log.info("补充数据耗时={}", stopWatch.getTotalTimeSeconds());
    }

更新数据

<update id="updateData">
        update
            test_table1 tcr
            left join test_table2 ttc on
            tcr.oir_id = ttc.oir_id
            set
                tcr.oir_name = ttc.name
         where
             tcr.stat_datetime between DATE_FORMAT(#{startDate,jdbcType=VARCHAR},'%Y-%m-%d 00:00:00') and DATE_FORMAT(#{startDate,jdbcType=VARCHAR},'%Y-%m-%d 23:59:59')
            and tcr.id >=#{minId}
            <if test="maxId!=null">
                and  tcr.id <![CDATA[ < ]]>  #{maxId}
            </if>
            <if test="userId!=null and userId!=''">
                and tcr.user_id = #{userId}
            </if>
    </update>