[MySQL]개발자라면 반드시 알아야하는 EVENT SCHEDULE

시작하면서...

(삽질마니해따...)Event_Scheduler와 MySQL 프로시저를 사용한 이유는 회사제품 수집기에서 수집해온 데이터가 쌓이는 테이블에서 다른 테이블로 데이터를 정제해서 이관하기 위해서 사용하였습니다. 이렇게 알게된 내용을 공유합니다.

 

영상

이 영상이 도움이 되셨다면 좋아요와 구독 한번씩 부탁드립니다. 

https://www.youtube.com/watch?v=Cb9_ltbM_Jc

준비사항

 MySQL5.7 

 MySQL WorkBench 

 _의의으쥐지_ 

1. 프로시저(PROCEDURE) 생성하기

프로시저는 MySQL에서의 메소드(함수)라고 보시면 됩니다. 실행할 여러 쿼리문이나 조건문이 들어가야 하는 쿼리문 반복문도 함께 사용할 수 있습니다.  

1-1 프로시저 실제 사용 예시

DELIMITER $$
DROP PROCEDURE IF EXISTS extractFromExtdata_content$$
CREATE PROCEDURE extractFromExtdata_content1()
BEGIN
    DECLARE i INT DEFAULT (SELECT extdata_id FROM if_extdata limit 1);
    SET SQL_SAFE_UPDATES=0;
    DELETE FROM tbl_infodata111;  -- tbl_infodata 테이블 비우기 
    alter table tbl_infodata111 auto_increment=0;  -- tbl_infodata 자동 증가 id 0으로 초기화
    WHILE (i <= (SELECT extdata_id FROM if_extdata order by 1 desc limit 1)) 
    DO
IF ((SELECT extdata_writedate FROM if_extdata where extdata_id=i) ='' ||(SELECT extdata_writedate FROM if_extdata WHERE extdata_id=i)= null) THEN
        SET @realdate = date_format(curdate(), "%Y%m%d%h%i%s");
    ELSE SET @realdate = date_format((SELECT extdata_writedate FROM if_extdata WHERE extdata_id=i), "%Y%m%d%h%i%s");
    END IF;
        INSERT INTO tbl_infodata111(title,date,image,linkurl,content,attach,url,siteId,seedName) VALUE(
       (SELECT extdata_title FROM if_extdata WHERE extdata_id=i),
       @realdate,
       (SELECT extdata_cururl FROM if_extdata WHERE extdata_id=i),
       (SELECT ExtractValue((SELECT extdata_content FROM if_extdata WHERE extdata_id=i), '//DataItem[./@name="EXTRACTHREF"]')),
       (SELECT ExtractValue((SELECT extdata_content FROM if_extdata WHERE extdata_id=i), '//DataItem[./@name="content"]')),
       (SELECT ExtractValue((SELECT extdata_content FROM if_extdata WHERE extdata_id=i), '//DataItem[./@name="attach"]//Url')),
       (SELECT ExtractValue((SELECT extdata_content FROM if_extdata WHERE extdata_id=i), '//DataItem[./@name="url"]')),
       (SELECT site_id FROM if_extdata where extdata_id=i),
       (SELECT seed_name FROM if_seed WHERE seed_id = (SELECT seed_id FROM if_extdata WHERE extdata_id = i))
);
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER $$

 

1-2 프로시저 작성 구조

BEGIN안에서 작성되는 쿼리들은 한 쿼리마다 세미콜론(;)으로 쿼리 구분을 해줘야 한다.

 DROP PROCEDURE IF EXISTS 프로시저명 : 기존에 프로시저가 존재한다면 프로시저를 삭제한다.

 CREATE PROCEDURE 프로시저명()  : 프로시저를 생성한다.

 BEGIN  : 프로시저의 로직을 BEGIN 다음부터 입력해준다.

 DECLARE  : 프로시저 안에서 변수를 생성해준다.

 SET  : 프로시저 안에서 DECLARE로 선언된 변수의 값을 변경해 준다.

 WHILE(Boolean)  : 프로시저에서 BEGIN~END 사이에 들어갈 수 있으며, 반복으로 실행될 쿼리문을 입력한다.

 END WHILE;  : WHILE문이 끝난다.

 IF(Boolean)  : 프로시저 내부에 조건문 인수로 쿼리문을 사용할 수 있고, 조건연산자로 Boolean이 리턴되는 형태로 만들어 줘야 함.

 END IF;  : 

 END  : 프로시저의 로직이 끝난다.

1-3 프로시저에서 조건문 사용하기

IF(boolean)

END IF;

프로시저에서  IF() 사용 예시

BEGIN
IF ((SELECT extdata_writedate FROM if_extdata where extdata_id=i) ='' || (SELECT extdata_writedate FROM if_extdata WHERE extdata_id=i)= null) 
    THEN
        SET @realdate = date_format(curdate(), "%Y%m%d%h%i%s");
    ELSE 
        SET @realdate = date_format((SELECT extdata_writedate FROM if_extdata WHERE extdata_id=i), "%Y%m%d%h%i%s");
END IF;
END$$

1-4 프로시저에서 반복문 사용하기

프로시저 안에서의 반복문은  WHILE() 함수를 사용한다. WHILE()함수의 인수로는 BOOLEAN이 들어간다. TRUE가 아닌 FALSE인 경우에 WHILE 반복을 벗어나게 된다. TRUE가 입력되면 무한 반복된다. 

 

사용 예시 0부터 10까지 값을 INSERT시킨다.

BEGIN
DECLARE i =0;
WHILE(i < 10)
    DO
    INSERT INTO 테이블(NUMBER) VALUES(i);
    SET i = i+1;
END WHILE;
END

2. MySQL에 event_scheduler 활성화 시키기

MySQL WORKBENCH켠다.

WORKBENCH의 sql 입력창에 아래의 event_scheduler 상태 확인 쿼리를 입력한다.

 SHOW VARIABLES LIKE 'event%'; 

event_scheduler 상태 확인 결과 'ON'이어야 스케줄 실행이 가능함.

4개의 쿼리를 실행시켜서 MySQL에서 event_scheduler가 실행될 수 있는 설정으로 변경해 줍니다.

 SET GLOBAL event_scheduler = ON; 

 SET @@global.event_scheduler = ON; 

쿼리 실행후 결과... 정상적

 

3. 정상적으로 event_scheduler가 생성되었는지 확인한다.

workbench의 sql 입력창에 아래의 event_scheduler 확인 쿼리를 입력한다.
SELECT * FROM information_schema.events;

 

event_scheduler 시간 설정

매일  EVERY 1 DAY

10분 마다 실행 EVERY 10 MINUTE

 

 AT 

특정 시간에 1회만 실행되는 예약어이다. 매일같이 실행되어야 하는 스케줄러에는 작성되면 안된다.

 

event_scheduler 작성 예시

 if_extdata에 있는 데이터를 tbl_infodata로 옮기는 프로시저(extractFromExtdata_content())를 매일 하루 새벽 4시 5분에 실행하는 이벤트 스케줄 생성

스케줄러 이름 : evtImportData -> 다른 스케줄러 이름과 중복 불가

스케줄 실행 시간 :  ON SCHEDULE EVERY 1 DAY STARTS '2020-01-30 04:05:00' 

실행하는 프로시저 :  DO CALL extractFromExtdata_content() 

 

-- 수집 스케줄
CREATE
   EVENT evtImportData
    ON SCHEDULE EVERY 1 DAY
    STARTS '2020-01-30 04:05:00'
    DO
    CALL extractFromExtdata_content();

 

이벤트 스케줄러 목록 조회하는 쿼리

 SHOW EVENTS 

or

 SELECT * FROM information_schema.events; 

 

이벤트 스케줄러 삭제 쿼리

 DROP EVENT 이벤트스케줄명; 

 

 

이 글이 도움이 되셨다면 댓글 부탁드립니다.