Mysql Database Event and Procedure(1)MySQL Event
Check Event Switch
>show variables like '%scheduler%';
event_scheduler OFF
or
event_scheduler ON
Start the event
>SET GLOBAL event_scheduler = 1;
Need to set my.cnf as follow, otherwise, after reboot the mysql database, the settings will be gone.
event_scheduler=ON
Event Grammer - Create Event
Call Procedure Every 9 days
CREAT EVENT EVENT1
ON SCHEDULE EVERY 9 DAY STARTS NOW()
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL TOTAL();
END
Grammer
CREATE
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE scheudle
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT ‘comment']
DO event_body;
schedule grammar:
AT timestamp [+INTERVAL interval] ..
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] … ]
[ENDS timestamp [+ INTERVAL interval] … ]
interval grammar:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK
| SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND
| HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
ON COMPLETION PRESERVE execute many times forever
Event Grammar - Change the Event
ALTER
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT ‘comment']
[DO event_body]
Event Grammer - Delete the Event
DROP EVENT [IF EXISTS] event_name
Some Samples - Event and SQL in Body
-- check switch
show variables like '%scheduler%';
-- enable switch
SET GLOBAL event_scheduler = 1;
-- test table
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`t1` datetime DEFAULT NULL,
`id2` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8
-- insert 1 record to that table every 3 seconds
CREATE EVENT IF NOT EXISTS test ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO test(id,t1) VALUES(NULL,NOW());
-- check event
SHOW CREATE EVENT test;
-- alter the event
ALTER EVENT test ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO test(id,t1) VALUES(NULL,NOW());
-- clean the things after 10 minutes
CREATE EVENT IF NOT EXISTS test2
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
DO TRUNCATE TABLE test;
DROP EVENT test;
-- check event
SHOW CREATE EVENT test2;
Some Samples - Event Call Procedure
define a store procedure and call the procedure after CALL
procedure name test_add();
CREATE EVENT test ON SCHEDULE EVERY 1 DAY
STARTS ‘2016-12-01 00:00:00'
ENDS ‘2016-12-01 00:00:00’ + INTERVAL 40 DAY
ON COMPLETION PRESERVE DO
CALL test_add();
Start and Disable Event
-- disable the event
ALTER EVENT test DISABLE;
-- enable the event
ALTER EVENT test ENABLE;
References:
https://blog.tankywoo.com/2015/04/01/mysql-stored-procedure.html
http://blog.chinaunix.net/uid-20639775-id-3323098.html
Check Event Switch
>show variables like '%scheduler%';
event_scheduler OFF
or
event_scheduler ON
Start the event
>SET GLOBAL event_scheduler = 1;
Need to set my.cnf as follow, otherwise, after reboot the mysql database, the settings will be gone.
event_scheduler=ON
Event Grammer - Create Event
Call Procedure Every 9 days
CREAT EVENT EVENT1
ON SCHEDULE EVERY 9 DAY STARTS NOW()
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL TOTAL();
END
Grammer
CREATE
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE scheudle
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT ‘comment']
DO event_body;
schedule grammar:
AT timestamp [+INTERVAL interval] ..
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] … ]
[ENDS timestamp [+ INTERVAL interval] … ]
interval grammar:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK
| SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND
| HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
ON COMPLETION PRESERVE execute many times forever
Event Grammar - Change the Event
ALTER
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT ‘comment']
[DO event_body]
Event Grammer - Delete the Event
DROP EVENT [IF EXISTS] event_name
Some Samples - Event and SQL in Body
-- check switch
show variables like '%scheduler%';
-- enable switch
SET GLOBAL event_scheduler = 1;
-- test table
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`t1` datetime DEFAULT NULL,
`id2` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8
-- insert 1 record to that table every 3 seconds
CREATE EVENT IF NOT EXISTS test ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO test(id,t1) VALUES(NULL,NOW());
-- check event
SHOW CREATE EVENT test;
-- alter the event
ALTER EVENT test ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO test(id,t1) VALUES(NULL,NOW());
-- clean the things after 10 minutes
CREATE EVENT IF NOT EXISTS test2
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
DO TRUNCATE TABLE test;
DROP EVENT test;
-- check event
SHOW CREATE EVENT test2;
Some Samples - Event Call Procedure
define a store procedure and call the procedure after CALL
procedure name test_add();
CREATE EVENT test ON SCHEDULE EVERY 1 DAY
STARTS ‘2016-12-01 00:00:00'
ENDS ‘2016-12-01 00:00:00’ + INTERVAL 40 DAY
ON COMPLETION PRESERVE DO
CALL test_add();
Start and Disable Event
-- disable the event
ALTER EVENT test DISABLE;
-- enable the event
ALTER EVENT test ENABLE;
References:
https://blog.tankywoo.com/2015/04/01/mysql-stored-procedure.html
http://blog.chinaunix.net/uid-20639775-id-3323098.html