MySQL事件的创建和执行
建立一个MySQL事件,首先我们需要建立一个或多个储存过程。示例如下
CREATE DEFINER=`vike0906`@`localhost` PROCEDURE `forTest`() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN SET @startTime = date_sub(curdate(),INTERVAL 0 DAY); SET @testSql=CONCAT("INSERT INTO hot_ali_bind (uid, channel, ali_num, ali_name, updatetime) (SELECT a.uid uid, b.channel channel, a.u_high ali_num, a.u_name ali_name, ? updatetime FROM hot_userinfo a LEFT JOIN hot_call_deduction_log b ON a.uid = b.userId WHERE a.id = 319) "); PREPARE stmt FROM @testSql; EXECUTE stmt USING @startTime; DEALLOCATE PREPARE stmt; END
过程体:
BEGIN SET @startTime = date_sub(curdate(),INTERVAL 0 DAY); SET @testSql=CONCAT("INSERT INTO hot_ali_bind (uid, channel, ali_num, ali_name, updatetime) (SELECT a.uid uid, b.channel channel, a.u_high ali_num, a.u_name ali_name, ? updatetime FROM hot_userinfo a LEFT JOIN hot_call_deduction_log b ON a.uid = b.userId WHERE a.id = 319) "); PREPARE stmt FROM @testSql; EXECUTE stmt USING @startTime; DEALLOCATE PREPARE stmt; END
紧接着我们来建立一个一分钟执行一次的事件:
CREATE DEFINER=`vike0906`@`localhost` EVENT `ceshi` ON SCHEDULE EVERY 1 MINUTE STARTS '2018-03-22 16:53:23' ON COMPLETION PRESERVE ENABLE COMMENT '我是备注' DO BEGIN CALL forTest(); END
一个事件可以执行多个储存过程,只需要添加CALL xx();就行
在时间建立完成后发现事件并没有按我们设想的去执行,此时我们需要去查看数据库的事件是否开启(两条SQL作用一样)
show variables like 'event%'; show variables like 'event_scheduler';
结果如图:
Variable_name | Value |
event_scheduler | OFF |
此时我们需要开启事件(两条SQL作用一样):
set global event_scheduler = NO; set global event_scheduler = 1;
至此我们发现事件已经能够按我们设想的那样执行了。
另.
在my.cnf中添加event_scheduler=ON,可以使MySQL数据库在重启时事件自动开启,Windos下是my.ini,在MySQL的安装目录。