[MySQL] 이벤트 스케쥴러와 프로시저 (매일 특정 시간에 CRUD 작업 실행)

MySQL에 주기적으로 특정시간에 DB에 어떤 작업을 해주어야 할 상황이 생길 때가 있다.

나는 이번에 쇼핑몰 프로젝트를 진행하면서  "장바구니에 담긴 상품은 최대 15일까지 보관되며 자동으로 삭제됩니다."

라는 장바구니의 정책이 있어서 이를 MySQL의 이벤트 스케쥴러와 프로시저를 활용하여 매일 자정에 15일이 지난 상품을 장바구니 테이블에서 삭제하도록 만들었다.

---

프로시저(Procedure)

프로시저는 MySQL의 함수(메서드)를 만드는 것이라고 보면 된다.

실행할 쿼리문을 함수로 빼서 재사용가능하고, 이 프로시저의 쿼리에 조건문이나 반복문도 함께 사용할수가 있다.

 

프로시저 생성

나같은 경우에는 현재날짜와 cart 테이블에 데이터가 생성된 시점을 비교해 15일이 지났으면 삭제하는 간단한 프로시저를 만들었다.

DELIMITER //
CREATE PROCEDURE deleteOldCartItems()
BEGIN
    DELETE FROM `cart` WHERE DATEDIFF(NOW(), `CART_CREATED_AT`) >= 15;
END;
//
DELIMITER ;

• DELIMITER : 프로시저와 관련은 없는 명령어지만 문장을 구분 하는 "구문 문자"로 사용되었다. 
• CREATE PROCEDURE : 프로시저를 생성
• BEGIN : 프로시저의 로직을 BEGIN 다음에 입력
• END : 프로시저의 로직이 끝나는 부분

 

프로시저 확인

SHOW PROCEDURE STATUS WHERE Db = '데이터베이스이름';

 

프로시저 삭제

DROP PROCEDURE IF EXISTS deleteOldCartItems;

---

이벤트 스케쥴러(Event Scheduler)

MySQL의 event는 SQL문을 포함하는 객체로 DB에 저장되어 1회 또는 설정한 기간마다 반복해서 실행된다.

MySQL의 이벤트를 추가하는 방법은 다음과 같다.

 

우선 mysql의 sql입력창에 아래의 쿼리를 입력해 이벤트 스케쥴러를 사용가능한 상태인지 아닌지를 확인하고, OFF라면 그다음 쿼리를 통해 ON으로 변경해주자.

SHOW VARIABLES LIKE 'event%';

SET GLOBAL event_scheduler = ON;

 

이벤트 등록

CREATE EVENT IF NOT EXISTS delete_old_cart_products
    ON SCHEDULE EVERY 1 DAY
    STARTS (CURRENT_DATE + INTERVAL 1 DAY)
    COMMENT 'cart에서 매일 자정에 15일이 지난 상품 제거'
    DO
    CALL deleteOldCartItems();

• CREATE EVENT IF NOT EXISTS : 이벤트 이름은 DB에서 유일해야 하므로 존재하는 지 확인한다음 생성하도록 하였다.
• ON SCHEDULE : 매일 이벤트가 생성되도록 하였다. 만약, 1회성인 경우 ON SCHEDULE AT 이벤트실행시간 을 적어주면 된다.
• STARTS : 이벤트 시작 시점으로, 현재 날짜로부터 1일 뒤인 다음 날 자정부터 시작하도록 하였다.
• COMMENT : 이벤트에 대한 주석.
• DO : 이 키워드 뒤에 실행할 SQL문을 작성한다.
• CALL :  SQL문을 작성하는 대신 위에서 만든 프로시저를 호출하도록하였다.

 

이벤트 확인

show events;

 

이벤트 삭제

DELETE FROM mysql.event
    WHERE db = 'db이름'
      AND name = '이벤트이름';