마감 기한(registration_due_date)이 지난 미션에 대해 이벤트를 실행합니다.
MISSION_PROCEEDING to MISSION_FINISHED
CREATE EVENT update_mission_from_proceeding_to_finished
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP - INTERVAL MINUTE(CURRENT_TIMESTAMP) MINUTE + INTERVAL 1 HOUR
DO
UPDATE mission
SET mission_status = "MISSION_FINISHED"
WHERE mission_status = "MISSION_PROCEEDING"
AND mission_id IN (
SELECT mission_id
FROM
(
SELECT mission.mission_id, max_people_number, COUNT(if(status = 'MISSION_FINISHED' OR status = 'FEEDBACK_REVIEWED', 1, null)) AS count_people_number
FROM mission
LEFT JOIN mission_registration mr on mission.mission_id = mr.mission_id
GROUP BY mission_id
) registration_status
WHERE max_people_number <= count_people_number
);
SELECT mission.mission_id, max_people_number, COUNT(if(status = 'MISSION_FINISHED' OR status = 'FEEDBACK_REVIEWED', 1, null)) AS count_people_number
FROM mission
LEFT JOIN mission_registration mr on mission.mission_id = mr.mission_id
GROUP BY mission_id;
미션 등록 별 최대 인원과 미션을 마친 인원들의 수를 조회합니다.
COUNT(if(status = 'MISSION_FINISHED' OR status = 'FEEDBACK_REVIEWED', 1, null)) AS count_people_number : 미션을 마친 상태에 대해서만 카운트합니다.
Do ... - 2
SELECT mission_id
FROM
(
-- ...
) registration_status
WHERE max_people_number <= count_people_number;
count_people_number가 max_people_number에 도달했을 경우에 대해서만 mission_id를 반환합니다.
개요
Event Scheduler
RECRUITING
toMISSION_PROCEEDING
CREATE EVENT update_mission_from_recruiting_to_proceeding
ON SCHEDULE EVERY 1 HOUR ~~ INTERVAL 1 HOUR
Do ...
RECRUITING
인 미션registration_due_date
)이 지난 미션에 대해 이벤트를 실행합니다.MISSION_PROCEEDING
toMISSION_FINISHED
CREATE EVENT update_mission_from_proceeding_to_finished
ON SCHEDULE EVERY 1 HOUR ~~ INTERVAL 1 HOUR
Do ...
- 1최대 인원
과미션을 마친 인원
들의 수를 조회합니다.COUNT(if(status = 'MISSION_FINISHED' OR status = 'FEEDBACK_REVIEWED', 1, null)) AS count_people_number
: 미션을 마친 상태에 대해서만 카운트합니다.Do ...
- 2count_people_number
가max_people_number
에 도달했을 경우에 대해서만 mission_id를 반환합니다.ETC
mysql에서 이벤트 스케줄러 사용하기
실행중인 이벤트 조회하기