Purpose
This statement is used to create and schedule a new event.
Notice
The EVENT feature is disabled by default. You must execute the SET GLOBAL event_scheduler = 1; command to enable it. For more information about this parameter, see event_scheduler.
Privilege requirements
The user executing this statement must have the CREATE privilege. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
CREATE
[DEFINER = user]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE] // By default, it is enabled after creation.
[COMMENT 'string']
DO event_body;
schedule: {
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
}
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
Usage instructions and considerations
- If
EVERY INTERVALis used without specifyingSTART TIME, the task will run indefinitely. - For repeated events, if the event execution time exceeds the scheduling interval, multiple events may occur at the same time. You must ensure that the execution time is less than the scheduling interval.
- The maximum execution time cannot be set (the maximum execution time for a one-time event is 24 hours).
- It is not recommended to create another event from one event.
- The schedule of an ongoing event cannot be modified.
- The
event_nameof an already started event should not be modified, otherwise the event may fail to end correctly. - If the event fails to execute consecutively for 16 times, it will stop running.
Parameters
Parameter |
Description |
|---|---|
| IF NOT EXISTS | It indicates that if the event already exists, it will not be created. When creating an event, if the event exists and no IF NOT EXISTS, an error will be reported. |
| event_name | Specifies the name of the event to be created. |
| DEFINER | Optional. Specifies the user who executes the event. Custom users are not supported. The default is the current user. |
| ON SCHEDULE schedule | Defines the time and frequency for event execution. Timestamp + INTERVAL is supported, for example, "2020-12-01 00:00:00" + INTERVAL 1 DAY. The time must be an integer, and the time units supported are YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. |
| ON COMPLETION [NOT] PRESERVE | Specifies whether to retain the event after it is executed. The default is NOT PRESERVE, which means the event is automatically deleted after execution. |
| ENABLE, DISABLE, DISABLE ON SLAVE | Defines the status of an event. By default, after an event is created, it is in the ENABLE, which means the event is enabled immediately after creation. DISABLE After an event is created, it is disabled and will not be executed automatically until it is manually enabled. DISABLE ON SLAVE Used only in a replication environment. The event is not executed on the server. |
| COMMENT 'string' | Optional. Add a comment for the event. |
| DO event_body | Defines the SQL statement to be executed. Currently, only single SQL statements are supported. BEGIN END Multiple SQL statements. |
Examples
obclient> CREATE EVENT myevent
ON SCHEDULE AT '2024-07-01 00:00:00' + INTERVAL 1 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;
References
- EVENT: Overview of events.
- ALTER EVENT : modifies an event.
- DROP EVENT: Drops an event.