Mysql 定时器的使用

select * from integral_info_temp;
select * from integral_info;
drop table integral_info_temp
create table integral_info_temp as select * from integral_info where 1!=1
delete from integral_info_temp;
select count(*) from student;

select * from student;
DROP PROCEDURE IF EXISTS mypro;
create procedure mypro()
BEGIN

– 定义变量
DECLARE s int DEFAULT 0;
DECLARE userId BIGINT;
DECLARE enterpriseId BIGINT;
DECLARE eduBackGround varchar(256) character set utf8 default NULL;

-- 定义游标，并将sql结果集赋值到游标中
DECLARE interUser CURSOR FOR  SELECT
																u.user_id ,
																u.enterprise_id ,
																di.`name`
															FROM
																sys_user u
															LEFT JOIN sys_dict di ON di.`code` = u.edu_background
															WHERE
																u.user_id != 1
															AND u.enterprise_id IS NOT NULL;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
-- 打开游标
open interUser;
	-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
	fetch interUser into userId,enterpriseId,eduBackGround;
	-- 当s不等于1，也就是未遍历完时，会一直循环
	while s<>1 do
		-- 执行业务逻辑
				SELECT
									count(*) into @cnt
									FROM
										detail_rule de
									LEFT JOIN intergral_rule ru ON de.intergral_rule_id = ru.id
									LEFT JOIN intergral_type ty ON ty.id = ru.intergral_type_id
									WHERE
										ty.type_name LIKE '%A分%'
									AND ty.enterprise_id = enterpriseId
									AND de.rule_detail
									= eduBackGround limit 1;
			if @cnt !=0 then        
				SELECT
					ty.enterprise_id,
					ty.type_name,
					de.rule_detail,
					de.interal_val,
					de.interal_val_type,
					de.id,ty.id intergral_type
        into @enterprise_id,@type_name,@rule_detail,@interal_val,@interal_val_type,@id,@intergralType
				FROM
					detail_rule de
				LEFT JOIN intergral_rule ru ON de.intergral_rule_id = ru.id
				LEFT JOIN intergral_type ty ON ty.id = ru.intergral_type_id
				WHERE
					ty.type_name LIKE '%A分%'
				AND ty.enterprise_id = enterpriseId
				AND de.rule_detail
				= eduBackGround limit 1;
      INSERT into integral_info (intergral_val,intergral_type,intergral_partType, intergral_source, create_time, intergral_preson, intergral_source_id, interal_val_type, create_author)
            values(@interal_val,@intergralType,@id,'XT_JF',now(),userId,null,@interal_val_type,1);

    end if; 
				
    

		-- 将游标中的值再赋值给变量，供下次循环使用
	fetch interUser into userId,enterpriseId,eduBackGround;

	-- 当s等于1时表明遍历以完成，退出循环
	end while;
-- 关闭游标
close interUser;

end;
#启动定时器
call mypro();
SET GLOBAL event_scheduler = 1;
1
#停止定时器

SET GLOBAL event_scheduler = 0;
#创建mysql的定时器event，这里设置为每一秒执行一次

create event if not exists eventJob
on schedule every 1 hour #EVERY 后面的是时间间隔，可以选 1 second，3 minute，5 hour，9 day，1 month，1 quarter（季度），1 year
on completion PRESERVE
do call mypro();

1
2
3
4
#2018-05-29 20:10:00启动定时器，每隔12小时执行一次

create event2 if not exists eventJob
on schedule every 12 hour starts timestamp ‘2018-05-29 20:10:00’
on completion PRESERVE
do call mypro();

#每个月的一号凌晨1 点执行
create event if not exists eventJob
on schedule every 1 month starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 month),interval 1 hour)
on completion PRESERVE
do call mypro();
1
2
3
4
#启动定时器

SET GLOBAL event_scheduler = 1;
1
#停止定时器

SET GLOBAL event_scheduler = 0;
1

开启事件

ALTER EVENT eventJob ON COMPLETION PRESERVE ENABLE;
1
#关闭事件

ALTER EVENT eventJob ON COMPLETION PRESERVE DISABLE;
1

查看定时器状态

SHOW VARIABLES LIKE ‘%sche%’;
1

设置定时器开机自启动

#找到etc/my.cnf 打开 在[mysqld]底下添加 event_scheduler = 1;