MySQL) Event Scheduler

Wonjun Lee·2025년 6월 28일
DBMSPL/SQLmysql

1. MySQL 서버에서 Event Scheduler 사용 여부를 체크.

SHOW VARIABLES LIKE 'event%';

-- 만약 OFF 라면,
SET GLOBAL event_scheduler = ON;

SHOW EVENTS; -- MySQL에 저장된 Event Scheduler 확인.

SELECT * FROM information_schema.EVENTS;

2. Event Scheduler 작성.

공식 문서 상에서 정의 :

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;

schedule: {
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]
}

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

https://dev.mysql.com/doc/refman/8.0/en/create-event.html

CREATE EVENT requires the EVENT privilege for the schema in which the event is to be created.

이벤트가 만들어지는 스키마에서 이벤트 정의를 위해 권한이 필요하다.

다음은 이벤트 스케쥴러 정의를 위한 최소 요구사항이다.

The minimum requirements for a valid CREATE EVENT statement are as follows:

  • The keywords CREATE EVENT plus an event name, which uniquely identifies the event in a database schema.
  • An ON SCHEDULE clause, which determines when and how often the event executes.
  • A DO clause, which contains the SQL statement to be executed by an event.
CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

ON SCHEDULE 구문이 이벤트가 언제, 얼마나 자주 실행될지를 정의하는 역할을 한다.

DO 구문은 이벤트 발생시 동작할 SQL 문장들이 위치한다.

위 예시의 이벤트는 테이블 생성 뒤 1시간 후 한 번 발생하게 된다.

이벤트 스케쥴러 이름은 대소문자를 구분하지 않는다.

event_name에 특정 스키마를 지정하지 않으면 현재 포함된 스키마로 자동 할당된다. 특정 스키마를 지정하기위해 schema.event_name을 활용할 수 있다.

If the DEFINER clause is omitted, the default definer is the user who executes the CREATE EVENT statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.
Within an event body, the CURRENT_USER function returns the account used to check privileges at event execution time, which is the DEFINER user.

작업 자체를 적절한 USER로 수행한다면 딱히 DEFINER는 신경쓰지 않아도 될 것 같다.

ON SCHEDULE

  1. timestamp : AT

    1번 실행되는 이벤트를 위해 사용된다. 주어진 날짜와 시간에 대해서 수행됨.

    만약 현재 시간보다 과거를 지정하면 경고가 발생한다.

    image.png

    CURRENT_TIMESTAMP 함수를 지정하면, 이벤트 생성 즉시 발생하게 된다.

    현재 시간이후 특정 OFFSET 만큼 이후에 실행되도록 하고 싶다면, + INTERVAL 구문을 사용해야 한다.

    이때, + INTERVAL 구분은 2개 파트로 구성된다. (양, TIME UNIT)

    https://dev.mysql.com/doc/refman/8.0/en/expressions.html#temporal-intervals

    MICROSECOND 같이 사용할 수 없는 UNIT도 존재함.

    AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAY
-- 3주 하고 2일 뒤에 실행되도록 한다.각 부분은 + INTERVAL로 시작해야만 한다.

  2. repeat : EVERY

    작업을 특정시간마다 반복하도록 하고 싶을 때.

    To repeat actions at a regular interval, use an EVERY clause. The EVERY keyword is followed by an *interval* as described in the previous discussion of the AT keyword. (+ INTERVAL is not used with EVERY.) For example, EVERY 6 WEEK means “every six weeks”.

    비록 + INTERVAL이 EVERY 구문에서 허용되진 않지만, 복잡한 TIME UNIT 들은 그대로 사용이 가능하다.

    언제부터 시작할 지를 명시하는 방법은 STARTS 구문을 사용하는 것이다. 여기서 특정 TIMESTAMP를 적용할 수 있다. 또한, + INTERVAL로 그 시간 + 얼마나 뒤 부터 이벤트를 반복할지 결정이 가능하다.

     EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK
 
 EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE

    또 ENDS 구문으로 종료를 지정할 수 있다. 

    EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE 
							ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK

    If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. If this is undesirable, you should institute a mechanism to prevent simultaneous instances. For example, you could use the GET_LOCK() function, or row or table locking.

ON SCHEDULE 절에서는 MySQL의 내장 함수와 사용자 변수(user variables)를 사용하여 시간(timestamp) 또는 간격(interval) 값을 얻는 표현식을 사용할 수 있다.

하지만 다음은 사용할 수 없다:

  • 저장 함수(stored functions)
  • 로드 가능한 함수(loadable functions)
  • 테이블 참조(table references)

다만, SELECT ... FROM DUAL 문은 사용할 수 있다.

이러한 제한은 CREATE EVENT와 ALTER EVENT 둘 다에 적용된다.

저장 함수, 로드 가능한 함수, 테이블을 참조하는 경우 오류가 발생하며, 이는 버그 #22830에서 확인된 내용이다.

즉, built-in function, user variables, select … from dual 문은 사용 가능하다.

이벤트가 만료된 이후에는 즉시 DROP 된다. 또한, DISABLE 키워드, ENABLE 키워드로 이벤트 활성을 설정할 수 있다.

CREATE EVENT e_hourly
    ON SCHEDULE
      EVERY 1 HOUR
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;

COMMENT는 이벤트에 대한 설명을 명시할 수 있는 구문이며, String literal을 사용해 지정한다.

내용설명
sql_mode 고정이벤트 생성/수정 시의 설정이 실행 시점에도 유지됨
DO 안에 ALTER EVENT가능해 보이지만 실행 시 오류 발생
SELECT, SHOW단순 결과 반환은 효과 없음
저장 SELECT만 가능SELECT INTO, INSERT INTO ... SELECT는 가능
스키마 기본값이벤트가 속한 스키마가 기본, 다른 스키마는 명시 필요

여러개의 명령을 적용하고자 하면 delimiter를 사용하라.

delimiter |

CREATE EVENT e
    ON SCHEDULE
      EVERY 5 SECOND
    DO
      BEGIN
        DECLARE v INTEGER;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;

        SET v = 0;

        WHILE v < 5 DO
          INSERT INTO t1 VALUES (0);
          UPDATE t2 SET s1 = s1 + 1;
          SET v = v + 1;
        END WHILE;
    END |

delimiter ;

또, 직접 Event에서 혹은 Event로 파라미터를 전달할 방법은 없지만, Stored procedure를 호출할 수 있다.

CREATE EVENT e_call_myproc
    ON SCHEDULE
      AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
    DO CALL myproc(5, 27);

그리고 DEFINER의 권한에 따라 DO 구문 내에서 GLOBAL VARIABLES를 세팅할 수도 있다.

profile
Wonjun Lee
Hello
이전 포스트

[MySQL] Trigger

다음 포스트

JUnit, Mockito) org.mockito.exceptions.misusing.InvalidUseOfMatchersException: Invalid use of argument matchers!

0개의 댓글