Parminder Singh Ubhi

MYSQL 5.1(+) provides you option to setup the scheduler within MYSQL, so you need not to setup cron jobs and need not to create PHP script every time you have update anything just in Database tables. Tasks that can be possible through queries can be scheduled within MYSQL events.

When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time.

Sometimes it is also called temporal triggers, but let’s not confuse this with “temporary triggers” which is totally different things and we can discuss on it some other time.

Key points Event is covering:

• An event is uniquely identified by its name and the schema to which it is assigned.
• An event performs a specific action according to a schedule. This action consists of an SQL statement, which can be a compound statement in a BEGIN … END block if desired.
• An event’s timing can be either one-time or recurrent. A one-time event executes one time only. A recurrent event repeats its action at a regular interval, and the schedule for a recurring event can be assigned a specific start day and time, end day and time, both, or neither. (By default, a recurring event’s schedule begins as soon as it is created, and continues indefinitely, until it is disabled or dropped.)

If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. If this is undesirable, you should institute a mechanism to prevent simultaneous instances. For example, you could use the GET_LOCK() function, or row or table locking.

• Users can create, modify, and drop scheduled events using SQL statements intended for these purposes.
• An event’s action statement may include most SQL statements permitted within stored routines.

Events are executed by a special event scheduler thread.

Event Syntax

• New events are defined using the CREATE EVENT statement.
• The definition of an existing event can be changed by means of the ALTER EVENT statement.
• When a scheduled event is no longer wanted or needed, it can be deleted from the server by its definer using the DROP EVENT statement.


CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
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}