MySQL scheduled event 만들기

추가되어야하는 기능

쉘위헬스 프로젝트에 기능을 추가하고 리팩토링하면서 다음과 같은 문제가 발생했다.

매칭게시판에서 예약날짜가 오늘날짜이지만 예약시간이 현재시간보다 이전인 경우, 즉 오늘 날짜(1월 9일)로 예약된 게시물이지만 아무도 신청하지 않은 상태로 예약시간(오후 2시)이 이미 지난 경우(현재 오후 3시)에도 다른 유저가 신청할 수 있었다.

따라서, 데이터베이스에서 조건1: 예약날짜의 시간을 현재시간과 비교해 예약시간이 지났고, 조건2: 매칭되지 않았다면 DB의 Posts 테이블의 isMatched 칼럼을 2로 수정해주어야한다.

scheduled event

MySQL 이벤트는 SQL 선언을 포함하는 객체로 데이터베이스에 저장되어 1회 또는 설정한 기간마다 반복해서 실행된다. MySQL은 scheduled event를 실행하기 위해 event scheduler thread라는 특별한 스레드를 사용한다. MySQL 이벤트는 데이터베이스 테이블 최적화, 로그 정리 등의 경우에 유용하게 사용할 수 있다.

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

MySQL 이벤트 스케쥴러 사용가능 여부 확인, 변경하기

SHOW VARIABLES LIKE 'event%'; 
//event_scheduler 변수 값이 ON인 경우 사용할 수 있으며 OFF인 경우 다음과 같이 설정한다.
SET GLOBAL event_scheduler = ON;
SHOW PROCESSLIST; // ON으로 변경시 event scheduler thread가 활성화된다.

//event_scheduler를 비활성화할 수 있다. 
SET GLOBAL evnet_scheduler = OFF;
SHOW PROCESSLIST; // OFF로 변경시 event scheduler thread가 비활성화한다. 

MySQL event 생성하기

• 이벤트 이름은 DB에서 유일해야한다.
• 이벤트가 1회성인 경우 ON SCHEDULE AT timestamp [+ INTERVAL]과 같이 작성한다.
  • 1회성인 경우 ON COMPLETION PRESERVE 속성을 추가해주어 이벤트가 실행된 후에도 이벤트를 보존할 수 있다. (작성하지 않은 경우 이벤트는 자동으로 삭제된다.)
  • SHOW EVENTS FROM classicmodels;으로 이벤트를 확인할 수 있다.
• 이벤트가 반복되는 경우
  • EVERY intervals를 사용해 반복되는 간격을 작성해준다.
  • 이벤트가 특정시간에 시작해 특정시간까지만 반복되는 경우 STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL]를 추가할 수 있다.
  • STARTS만 작성하는 경우, 작성한 시간부터 이벤트가 시작된다.
• DO 키워드 뒤에 SQL문을 작성한다.

//기본 문법
CREATE EVENT IF NOT EXISTS 이벤트 이름 
ON SCHEDULE 스케줄
ON COMPLETION PRESERVE
DO
  실행시킬 내용 

MySQL event 삭제하기

DROP EVENT IF EXIST 이벤트 이름

작성한 코드

CREATE EVENT IF NOT EXISTS autoCancel 
ON SCHEDULE every 10 minute 
STARTS '2022-01-09 14:20:00' 
ON COMPLETION PRESERVE 
  COMMENT 'match has been canceled due to time limit' 
DO 
  UPDATE DB이름.Posts 
  SET isMatched=2 
  WHERE isMatched=0 AND reserved_at<=NOW();

reference

• https://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/
• https://jungeunpyun.tistory.com/64