SELECT count(*) FROM wish_sellers_in;
UPDATE wish_sellers_in SET act_status =0 WHERE id >=1 AND id <=70;
-- 查看事件
SHOW EVENTS;
-- 查看定时器状态
SHOW VARIABLES LIKE '%sched%';
-- 开启定时器
SET GLOBAL event_scheduler=1;
-- 查看
SHOW VARIABLES LIKE '%sched%';
-- 删除存储过程
DROP PROCEDURE IF EXISTS pro_update_seller_in_act_status;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE pro_update_seller_in_act_status(a INT ,b INT)
BEGIN
IF a=1 AND b=360 THEN
UPDATE wish_sellers_in SET act_status =1 WHERE id >=a AND id <=b;
END IF;
IF a=1 AND b=1500 THEN
UPDATE wish_sellers_in SET act_status =1 WHERE id >=a AND id <=b;
END IF;
IF a=1501 AND b=5000 THEN
UPDATE wish_sellers_in SET act_status =1 WHERE id >=a AND id <=b;
END IF;
IF a=5001 AND b=15000 THEN
UPDATE wish_sellers_in SET act_status =1 WHERE id >=a AND id <=b;
END IF;
IF a=15001 AND b=31542 THEN
UPDATE wish_sellers_in SET act_status =1 WHERE id >=a AND id <=b;
END IF;
IF a=51 AND b=60 THEN
UPDATE wish_sellers_in SET act_status =1 WHERE id >=a AND id <=b;
END IF;
IF a=61 AND b=70 THEN
UPDATE wish_sellers_in SET act_status =1 WHERE id >=a AND id <=b;
END IF;
END $$
DELIMITER ;
-- 删除计划任务
/*
DROP EVENT IF EXISTS event_update_sellers_in_act_status_id1;
CREATE EVENT event_update_sellers_in_act_status_id1
ON SCHEDULE
EVERY 5 DAY STARTS '2016-07-28 00:30:00'
ON COMPLETION PRESERVE
DO CALL pro_update_seller_in_act_status(1,10);
*/
DROP EVENT IF EXISTS event_update_sellers_in_act_status_id21;
CREATE EVENT event_update_sellers_in_act_status_id21
ON SCHEDULE
EVERY 5 DAY STARTS '2016-07-29 00:30:00'
ON COMPLETION PRESERVE
DO CALL pro_update_seller_in_act_status(5001,15000);
DROP EVENT IF EXISTS event_update_sellers_in_act_status_id31;
CREATE EVENT event_update_sellers_in_act_status_id31
ON SCHEDULE
EVERY 5 DAY STARTS '2016-07-30 00:30:00'
ON COMPLETION PRESERVE
DO CALL pro_update_seller_in_act_status(15001,31542);
DROP EVENT IF EXISTS event_update_sellers_in_act_status_id41;
CREATE EVENT event_update_sellers_in_act_status_id41
ON SCHEDULE
EVERY 5 DAY STARTS '2016-07-31 00:30:00'
ON COMPLETION PRESERVE
DO CALL pro_update_seller_in_act_status(1,360);
DROP EVENT IF EXISTS event_update_sellers_in_act_status_id51;
CREATE EVENT event_update_sellers_in_act_status_id51
ON SCHEDULE
EVERY 5 DAY STARTS '2016-08-01 00:30:00'
ON COMPLETION PRESERVE
DO CALL pro_update_seller_in_act_status(1,1500);
DROP EVENT IF EXISTS event_update_sellers_in_act_status_id61;
CREATE EVENT event_update_sellers_in_act_status_id61
ON SCHEDULE
EVERY 5 DAY STARTS '2016-08-02 00:30:00'
ON COMPLETION PRESERVE
DO CALL pro_update_seller_in_act_status(1501,5000);
/*
DROP EVENT IF EXISTS event_update_sellers_in_act_status_id71;
CREATE EVENT event_update_sellers_in_act_status_id71
ON SCHEDULE
EVERY 7 DAY STARTS '2016-08-03 00:30:00'
ON COMPLETION PRESERVE
DO CALL pro_update_seller_in_act_status(61,70);
*/
DROP EVENT IF EXISTS event_update_sellers_in_act_status_;
CREATE EVENT event_update_sellers_in_act_status_
ON SCHEDULE
EVERY 1 DAY STARTS '2016-07-28 12:37:00'
ON COMPLETION PRESERVE
DO CALL pro_update_seller_in_act_status(61,70);
-- 开启事件
ALTER EVENT event_update_sellers_in_act_status ON
COMPLETION PRESERVE ENABLE;
-- 查看存储过程状态
SHOW PROCEDURE STATUS;
-- 查看创建存储过程语句
SHOW CREATE PROCEDURE pro_update_seller_in_act_status;
-- 查看事件创建过程语句
show create event [event_update_sellers_in_act_status_id21];
-- 关闭事件
ALTER EVENT event_update_sellers_in_act_status ON
COMPLETION PRESERVE DISABLE;
-- 关闭 定时器
SET GLOBAL event_scheduler=0;
-- 查看定时器状态
SHOW VARIABLES LIKE '%sched%';
-- 删除事件
DROP EVENT [IF EXISTS] event_update_sellers_in_act_status;
修改事件(ALTER EVENT)
ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE]
[DO sql_statement]
创建表 CREATE TABLE test(endtime DATETIME);
创建存储过程test
DROP PROCEDURE IF EXISTS test;
CREATE PROCEDURE test ()
BEGIN
/*update examinfo SET endtime = now() WHERE id = 14; */
INSERT INTO test(endtime) VALUE (NOW());
END ;
创建event e_test
DROP EVENT IF EXISTS e_test;
CREATE EVENT if not exists e_test
on schedule every 3 second
on completion preserve
do call test();
每隔30秒将执行存储过程test,将当前时间更新到examinfo表中id=14的记录的endtime字段中去
-----AT '2007-09-01 12:00:00' + INTERVAL 1 DAY --只执行一次at
4.event的时间设置
设置event很简单，但是麻烦的是如何设置执行的时间，网上找了一些，自己总结了一下。
先看语句，如下面这个
CREATE EVENT test_event ON SCHEDULE EVERY 1 DAY STARTS '2012-09-24 00:00:00'
ON COMPLETION PRESERVE ENABLE DO CALL test_procedure();
EVERY 后面的是时间间隔，可以选 1 second，3 minute，5 hour，9 day，1 month，1 quarter（季度），1 year
从2013年1月13号0点开始，每天运行一次
ON SCHEDULE EVERY 1 DAY STARTS '2013-01-13 00:00:00'
从现在开始每隔九天定时执行
ON SCHEDULE EVERY 9 DAY STARTS NOW() ；
每个月的一号凌晨1 点执行
on schedule every 1 month starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 month),interval 1 hour);
每个季度一号的凌晨1点执行
on schedule every 1 quarter starts date_add(date_add(date(concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1)),interval 1 quarter),interval 1 hour);
每年1月1号凌晨1点执行
on schedule every 1 quarter starts date_add(date_add(date(concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1)),interval 1 quarter),interval 1 hour);
