Membuat event scheduler di MySQL

scheduler.sql

-- Aktifkan event scheduler
SET GLOBAL event_scheduler := 1;
SELECT @@event_scheduler;

-- Menampilkan events yang ada pada db
SHOW EVENTS;

-- Contoh
-- nama event: event_payments
CREATE EVENT `event_payments` ON SCHEDULE EVERY 1 MINUTE
ON COMPLETION PRESERVE ENABLE
DO
UPDATE
    payments
SET
    payments.payStatus = 'cancel'
WHERE
    NOW() > payments.expirationTime AND payments.payStatus = 'waiting_payment' AND payments.vaStatus = 'WAITING_PAYMENT'

-- A recurring event is one which executes repeatedly, once at start-time and once every interval thereafter. A one-time event is one which will execute only once, at start-time.

-- The scheduler stores event definitions in an Events table where they can be seen with the SHOW EVENTS command; each CREATE EVENT inserts a new record into this table. Normal system behaviour is that once a one-time event has passed, or a recurring event's end-time has passed, the event will be deleted from the table. There may be occasions, however, when one wishes to retain a record of past events, for example for review, or so that a particularly complex event can be rerun with ALTER EVENT instead of retyping the entire event definition. COMPLETION PRESERVE whether the event definition is deleted from the events table once it has completed, or kept. The default behaviour is to delete; checking this box causes the event to be preserved.


-- contoh lain
CREATE EVENT delete_candle_data ON SCHEDULE EVERY 3 DAY
DO
DELETE
FROM
    tx_candle_domain_model_candle
WHERE
    crdate < UNIX_TIMESTAMP(TIMESTAMPADD(DAY, -3, NOW()))
ORDER BY
    crdate
DESC

-- hapus event
DROP EVENT delete_candle_data;