CREATE EVENT
Sintassi
CREATE [DEFINER = { utente | CURRENT_USER }] EVENT [IF NOT EXISTS] nome_evento ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment0'] DO istruzione_sql; schedule: AT timestamp [+ INTERVAL interval] ... | EVERY intervallo [STARTS timestamp [+ INTERVAL interval] ...] [ENDS timestamp [+ INTERVAL interval] ...] intervallo: quantità {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
Spiegazione
Questa istruzione crea e pianifica un Evento. E' necessario il privilegio EVENT sullo schema nel quale si desidera creare l'Evento.
I requisiti minimi per poter eseguire CREATE EVENT sono i seguenti:
- Le parole chiave
CREATE EVENT
più il nome dell'evento, che identifica univocamente il nome dell'Evento nello schema corrente. (Prima di MySQL 5.1.12, il nome dell'Evento doveva essere univoco solo tra gli Eventi creati dallo stesso utente su un dato database) - Una clausola
ON SCHEDULE
, che stabilisce quando e quanto spesso l'Evento verrà eseguito. - Una clausola
DO
, che contiene l'istruzione SQL che l'Evento dovrà eseguire.
Ecco un esempio di istruzione CREATE EVENT
minimale:
CREATE EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO UPDATE myschema.mytable SET mycol = mycol + 1;
L'istruzione precedente crea un evento chiamato myevent. Questo evento viene eseguito una sola volta — un'ora dopo la sua creazione — ed esegue un'istruzione SQL che incrementa di 1 il valore della colonna mycol nella tabella myschema.mytable.
Il nome dell'Evento deve essere un identificatore valido di MySQL con una lunghezza massima di 64 caratteri. Può essere racchiuso tra i backtick, e può essere qualificato con il nome dello schema. Gli Eventi sono associati a un utente MySQL (il definer) e a uno schema, e il nome deve essere univoco all'interno dello schema. In generale, le regole che governano i nomi degli Eventi sono le stesse che regolano i nomi delle Stored Routine. Si veda http://dev.mysql.com/doc/refman/5.1/en/identifiers.html.
Se non viene indicato uno schema come parte di nome_evento, viene usato lo schema corrente.
La clausola ON SCHEDULE
serve a specificato quando l'Evento deve essere eseguito.
If you want to execute the event only once (one time event), you can use the AT
keyword, followed by a timestamp. If you use CURRENT_TIMESTAMP
, the event acts as soon as it is created. As a convenience, you can add one or more intervals to that timestamp. You can also specify a timestamp in the past, so that the event is stored but not triggered, until you modify it via ALTER EVENT.
The following example shows how to create an event that will be triggered tomorrow at a certain time:
CREATE EVENT example ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY + INTERVAL 3 HOUR DO something;
You can also specify that an event must be triggered at a regular interval (recurring event). In such cases, use the EVERY
clause followed by the interval.
If an event is recurring, you can specify when the first execution must happen via the STARTS
clause and a maximum time for the last execution via the ENDS
clause. STARTS
and ENDS
clauses are followed by a timestamp and, optionally, one or more intervals. The ENDS
clause can specify a timestamp in the past, so that the event is stored but not executed until you modify it via ALTER EVENT.
In the following example, next month a recurring event will be triggered hourly for a week:
CREATE EVENT example ON SCHEDULE EVERY 1 HOUR STARTS CURRENT_TIMESTAMP + 1 MONTH ENDS CURRENT_TIMESTAMP + 1 MONTH + 1 WEEK DO some_task;
Intervals consist of a quantity and a time unit. The time units are the same used for other staments and time functions, except that you can't use microseconds for events. For simple time units, like HOUR
or MINUTE
, the quantity is an integer number, for example '10 MINUTE'. For composite time units, like HOUR_MINUTE
or HOUR_SECOND
, the quantity must be a string with all involved simple values and their separators, for example '2:30' or '2:30:30'.
The ON COMPLETION
clause can be used to specify if the event must be deleted after its last execution (that is, after its AT
or ENDS
timestamp is past). By default, events are dropped when they are expired. To explicitly state that this is the desired behaviour, you can use ON COMPLETION NOT PRESERVE
. Instead, if you want the event to be preserved, you can use ON COMPLETION PRESERVE
.
In you specify ON COMPLETION NOT PRESERVE
, and you specify a timestamp in the past for AT
or ENDS
clause, the event will be immediatly dropped. In such cases, you will get a Note 1558: "Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation".
Events are ENABLE
d by default. If you want to stop MariaDB from executing
an event, you may specify DISABLE
. When it is ready to be activated, you
may enable it using ALTER EVENT
. Another option is
DISABLE ON SLAVE
, which prevents slave servers from executing the event.
The COMMENT
clause may be used to set a comment for the event. Maximum
length for comments is 64 characters. The comment is a string, so it must be
quoted. To see events comments, you can query the EVENTS
table in the
INFORMATION_SCHEMA
(the column is named EVENT_COMMENT
).