Example of ALTER Event MySql statement :
CREATE EVENT myevent
ON SCHEDULE AT
CURRENT_TIMESTAMP +
INTERVAL 1 HOUR
DO
UPDATE myschema.mytable
SET mycol = mycol + 1;
CREATE EVENT e_hourly
ON SCHEDULE
EVERY 1 HOUR
COMMENT 'Clears OUT sessions table EACH hour. '
DO
DELETE FROM site_activity.sessions;
|
|
delimiter |
CREATE EVENT e_daily
ON SCHEDULE
EVERY 1 DAY
COMMENT 'Saves total number of sessions
THEN clears the table
EACH day'
DO
BEGIN
INSERT INTO site_activity.totals (time, total)
SELECT CURRENT_TIMESTAMP, COUNT(*)
FROM site_activity.sessions;
DELETE FROM site_activity.sessions;
END |
delimiter ;
delimiter |
CREATE EVENT e
ON SCHEDULE
EVERY 5 SECOND
DO
BEGIN
DECLARE v INTEGER;
DECLARE
CONTINUE HANDLER
FOR SQLEXCEPTION BEGIN END;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t1
VALUES (0);
UPDATE t2
SET s1 = s1 + 1;
SET v = v + 1;
END WHILE;
END |
delimiter ;
CREATE EVENT e_call_myproc
ON SCHEDULE
AT
CURRENT_TIMESTAMP +
INTERVAL 1 DAY
DO
CALL myproc(5, 27);
Example of Mysql ALTER EVENT
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 sql_statement;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY INTERVAL
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
|
|
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 sql_statement;
schedule:
AT timestamp [+
INTERVAL interval] ...
| EVERY
INTERVAL
[STARTS timestamp [+
INTERVAL interval] ...]
[ENDS timestamp [+
INTERVAL interval] ...]