批量更新操作

背景：现有两张表，一张为指令配置表command（数据量为41条） ,一张为记录请求失败的表regist_error（数据量为60万），regist_error表有一type字段需要更新，来源于command表的sms_type，两表通过code字段关联。

笨方法，适合复杂操作（用多线程程序运行）

@Test
public void  getFiltes() throws InterruptedException {
    //获取指令表command 指定类型的指令
    List<SpBizCommand> allCommand = spBizCommandMapper.getAllCommand(3);

    List<RegistError> registErrors =
            registErrorMapper.selectList(new QueryWrapper<RegistError>().lambda().between(true,RegistError::getId,13472,13500));

        for(RegistError registError:registErrors){
	//处理数据
            registError.setType(allCommand.stream().filter(m-> m.getCodes().equals(registError.getCode())).findAny().get().getSmsType());
        }

    ExecutorService executor = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());
    CountDownLatch countDownLatch = new CountDownLatch(Runtime.getRuntime().availableProcessors());
    ImportTask task = new ImportTask(registErrors,countDownLatch);
    executor.execute(task);
    countDownLatch.await();
    executor.shutdown();

}
class ImportTask implements Runnable {
    private List<RegistError> list;
     private CountDownLatch countDownLatch;

       public ImportTask(List<RegistError> data, CountDownLatch countDownLatch) {
                  this.list = data;
                   this.countDownLatch = countDownLatch;
             }

           @Override
     public void run() {
                    if (null != list) {
                        for(RegistError registError:list){
                            //更新
                           registErrorMapper.update(null,new UpdateWrapper<RegistError>().lambda().eq(RegistError::getId,registError.getId()).set(RegistError::getType,registError.getType()));
                        }
                     }
                   // 发出线程任务完成的信号
                  countDownLatch.countDown();
               }
}

使用存储过程

BEGIN
DECLARE row_type INT;#定义类型
DECLARE row_id INT;#定义id
DECLARE done INT;
-- 定义游标
DECLARE rs_cursor CURSOR FOR
-- 整理获取待更新数据
SELECT error.id ,command.type  FROM regist_error error LEFT JOIN (SELECT	sms_type type,		codes	FROM		mtk_biz_command	WHERE		biz_type = 3 	AND delete_flag = 1) command ON error.`code` = command.codes WHERE	id BETWEEN start_id  AND end_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN rs_cursor; 
cursor_loop:LOOP
FETCH rs_cursor INTO row_id,row_type; -- 取数据
IF done=1 THEN
leave cursor_loop;
END IF;
-- 更新表
UPDATE regist_error SET type=row_type WHERE id=row_id;
END LOOP cursor_loop;
CLOSE rs_cursor;
END

调用：
CALL update_district_level(1,703359)