只适用于表的主键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>