mysql procedure 삭제 작업

1. 작업 대상에 대해 임시 테이블 생성

 

CREATE TABLE `tmp_200504` (
  seq  bigint(20) not null,
  endpoint_key bigint(20) not null,  
  del_is char(1) null,
  primary key (seq) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

-- 작업 대상이 되는 row를 임시테이블로 이관

insert into tmp_200504 select sequence, endpoint_key, null from tmp WHERE type = 'test';

 

 

 

2. 프로시저를 생성하여 delete 수행

 

-- mysql delimiter 변경

delimiter ;;

 

 

-- 10000건씩 짤라서 삭제하는 프로시저 생성

CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_batch_job`(in v_limit int)
begin
  declare v_done int default false;
  declare v_count int default 0;
  declare v_num   bigint default 0;
  declare v_seq  bigint(20);
  declare v_endpoint bigint(20);

  declare v_cur1 cursor for (
     select seq, endpoint_key
     from tmp_200504
     where del_is is null and seq <= v_limit
  );
  
  declare continue handler for not found set v_done = TRUE;

  open v_cur1;
  repeat  
  fetch v_cur1 into v_seq, v_endpoint;
  select v_done;
  
  if not v_done then
     set v_count = v_count + 1;
     
     delete from tmp where sequence = v_seq;  
     update tmp_200504 set del_is = 'Y' where seq = v_seq;
     
     if v_count % 1000 = 0 then
        select concat('commit : ', v_count);
        commit;
        select sleep(0.01);
     end if;     
     set v_done = false;
  end if;
  
  
  show warnings;
  until v_done end repeat;
  close v_cur1;
  
  commit;
end;;

 

-- mysql delimiter를 원래대로 변경
delimiter ;

 

 

3.  외부 shell로 배치 형태로 수행

 

for i in (1..100000..1000)

do

    mysql -u root -p   << EOF

 

    call delete_batch_job($i);

 

EOF

 

done