MySQL Events (Crons) einrichten

Man kann bei MySQL, sofern aktiviert, serverseitige Events einrichten. Das kann man bspw. nutzen, um Tabellenbereinigungen automatisiert durchzuführen.

Prüfen, ob der Event Scheduler aktiv ist

SQL

SHOW VARIABLES LIKE 'event_scheduler'; -- Ausgabe event_scheduler ON

SHOW VARIABLES LIKE 'event_scheduler'; -- Ausgabe event_scheduler ON

SQL

Manuell aktivieren

SQL

SET GLOBAL event_scheduler = ON;

SET GLOBAL event_scheduler = ON;

SQL

Event anlegen

SQL

CREATE EVENT [EVENT_NAME]
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE, '03:15:00')
DO
  DELETE FROM [TABLENAME]
  WHERE created < DATE_SUB(NOW(), INTERVAL 1 YEAR);

CREATE EVENT [EVENT_NAME]
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE, '03:15:00')
DO
  DELETE FROM [TABLENAME]
  WHERE created < DATE_SUB(NOW(), INTERVAL 1 YEAR);

SQL

Alle Events anzeigen

SQL

SHOW EVENTS;

SHOW EVENTS;

SQL

Details zu einem Event anzeigen

SQL

SHOW CREATE EVENT [EVENT_NAME];

SHOW CREATE EVENT [EVENT_NAME];

SQL

Prüfen, ob und wann Events gelaufen sind

SQL

SELECT 
    EVENT_NAME,
    LAST_EXECUTED,
    STATUS
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = '[DATENBANK_NAME]';

SELECT 
    EVENT_NAME,
    LAST_EXECUTED,
    STATUS
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = '[DATENBANK_NAME]';

SQL