MySQL Event Scheduler

학습

Event Scheduler 사용여부 확인

SHOW VARIABLES LIKE 'event%';

 

Scheduler ON/OFF

SET GLOBAL event_scheduler = ON;

 

Event Scheduler 확인

SELECT * FROM information_schema.EVENTS;

 

생성

• [ ]는 생략 가능
• event_name : 이벤트 이름
• schedule : 수행, 반복할 시간 및 기간
• event_body : 수행할 쿼리문

schedule

• AT : 수행할 시간
• EVERY : 반복할 시간
• STARTS, ENDS : 반복할 기간
• Interval 종류

---

실습

-- 1. Table CREATE 문
CREATE TABLE user_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    log_message VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

SHOW VARIABLES LIKE 'event%';
SET GLOBAL event_scheduler = ON;

-- 2-1. Event Scheduler1
CREATE EVENT add_log_every_minute
ON SCHEDULE EVERY 1 MINUTE
DO
INSERT INTO user_logs (username, log_message) 
VALUES ('user1', 'This is a log message added every minute');

-- 2-2. Event Scheduler2
CREATE EVENT clear_logs_daily
ON SCHEDULE EVERY 1 DAY 
STARTS '2024-12-25 00:00:00'
DO
DELETE FROM user_logs WHERE created_at < NOW() - INTERVAL 1 DAY;

-- 2-3. Event Scheduler3
CREATE EVENT weekly_log_for_user
ON SCHEDULE EVERY 1 WEEK 
STARTS '2024-12-25 09:00:00'
DO
INSERT INTO user_logs (username, log_message) 
VALUES ('admin', 'Weekly admin log');

-- 결과 
SELECT * FROM information_schema.EVENTS;