Event – Zadania w MySQL

Tworzenie procedury w MySQL np:
DELIMITER $$
CREATE PROCEDURE travels.MOVE_OLD_AUTOMAILER_RECORDS()
BEGIN
INSERT INTO anyDb.automailer_old(id, from_email, from_name, to_email, subject, body, alt_body, swift_message, created_at, sent_at, is_html, is_sending, is_sent, is_failed, started_sending_at)
SELECT * FROM anyDb.automailer WHERE sent_at <= (SELECT NOW() - INTERVAL 1 MONTH);
DELETE FROM anyDb.automailer WHERE sent_at <= (SELECT NOW() - INTERVAL 1 MONTH);
END $$
DELIMITER ;

Wywołanie przykładowej procedury:
CALL anyDb.MOVE_OLD_AUTOMAILER_RECORDS();

Zapisanie zadania:
CREATE EVENT MOVE_OLD_AUTOMAILER_RECORDS ON SCHEDULE EVERY 1 DAY STARTS current_timestamp COMMENT 'Przeniesienie rekordów starszych niż miesięczne do tabeli automailer_old' DO CALL MOVE_OLD_AUTOMAILER_RECORDS();

Wyświetlenie zadań z bieżącej bazy danych:
SHOW EVENTS FROM travel_2015_12;

Aktualizacja Eventu
ALTER EVENT MOVE_OLD_AUTOMAILER_RECORDS ON SCHEDULE EVERY 1 MONTH STARTS '2015-12-07 07:30:00';

Leave a Reply