mysql中使用事件和存储过程进行定时业务的执行

因公司项目中有个赠送优惠券功能，此功能需求为：赠送出去的优惠券若24小时内没有被领取，则自动设置为赠送失败（过期未被领取）,优惠券自动退换给赠送者。

初步想法是：在MySQL中创建存储过程进行业务逻辑判断以及操作库，然后使用事件定时的调取存储过程，即可完成需求。

一、创建事件

1.、事件简单描述就是可以定时的执行一个任务，最大的优点是：一些定时操作不在依赖外部程序，而直接使用数据库的自身提供的功能完成，可以实现每秒钟执行一个任务，这对一些实时性要求很高的环境就非常实用。缺点是：不可以手动调用，定时触发。

2、首先查看MySQL是否开始事件

2.1 查看数据库是否开始事件

SHOW VARIABLES LIKE "event_scheduler";

查询结果如下：

2.2 如果value = off 则需要通过如下任何一个命令进行事件的开启(若要关闭则为 0 或者 off)：

SET GLOBAL event_scheduler = ON;  
SET @@global.event_scheduler = ON;  
SET GLOBAL event_scheduler = 1;  
SET @@global.event_scheduler = 1; 

通过配置文件my.conf(若要关闭则为 0 或者 off)

event_scheduler = 1 #或者ON

3、创建事件event

DELIMITER $$

-- SET GLOBAL event_scheduler = ON$$     -- required for event to execute but not create    

CREATE	/*[DEFINER = { user | CURRENT_USER }]*/	EVENT `weishop`.`e_return_conpon`

ON SCHEDULE
	 /* uncomment the example below you want to use */

	-- scheduleexample 1: run once

	   --  AT 'YYYY-MM-DD HH:MM.SS'/CURRENT_TIMESTAMP { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] }

	-- scheduleexample 2: run at intervals forever after creation

	   -- EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]

	-- scheduleexample 3: specified start time, end time and interval for execution
	   /*EVERY 1  [HOUR|MONTH|WEEK|DAY|MINUTE|...]

	   STARTS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1[HOUR|MONTH|WEEK|DAY|MINUTE|...] }

	   ENDS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] } */

/*[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']*/
	EVERY 30 MINUTE 
DO
	BEGIN
	    CALL p_return_coupon();
	END$$

DELIMITER ;

4、名词解释： database_name：数据库名称

event_name：事件名称（唯一性）

on schedule：计划任务

schedule：决定event的执行时间和频率（注意时间一定是将来的时间，过去的时间会报错），有两种形式AT 和 EVERY。

[on completion [not] preserve]：可选项，默认是 on completion not preserve 即计划任务执行完毕自动 drop 该事

件，on completion preserve 则不会 drop 掉。 

[comment 'comment']： 可选项，注释。

[enable||disable]：设定event的状态，默认 enable ：表示系统尝试执行这个事件，disable：关闭改事件，可通过 

alter：命令修改。

do event_body：需要执行的sql语句，可以是复合sql，亦可以调用存储过程 procedure。

我们在上面创建了一个event，每隔30分钟调用一次 p_return_coupon 存储过程。

二、创建存储过程 p_return_coupon

1、存储过程的介绍：简单来说，存储过程就是为以后的使用而保存的一条或者多条MySQL语句的集合，存储过程中有业务逻

辑和流程的集合，可以在存储过程中创建表，更新数据，删除等等。

2、为什么需要使用存储过程：

2.1、通过把复杂的业务封装成一个单元，简化操作，程序中只需要 调用存储过程处理，比起单条 sql 执行，代码量和效

率会有所改善。

2.2、在生产环境可以直接修改存储过程来处理bug，不需要重启服务器（前提是修改的存储过程需要进行详细的测试）。

2.3、方便业务的变更维护，如果表明和列名发生变化，只需要修改存储过程，不用改变任何代码。

3、创建存储过程

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `weishop`.`p_return_coupon`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
	-- 声明一个标志done,用来判断游标是否遍历完成
	DECLARE done INT DEFAULT 0;
	-- 声明一个变量，用来存放从游标中提取的数据
	DECLARE cid INT;
	DECLARE ctime DATETIME;
	-- 声明一个游标
	DECLARE cur CURSOR FOR SELECT m.id FROM member_coupon m INNER JOIN given_record g ON m.id = g.coupon_Id WHERE m.status = 3 AND g.orders = 1 AND g.create_date IS NOT NULL;
	-- 在游标循环到最后会将 done 设置为 1(如果不加该行  游标为空的时候  就会报错)
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	-- 开启游标
	OPEN cur;
	-- 开始循环		
	REPEAT
		-- 游标的每一次循环值赋值给cid变量
		FETCH cur INTO cid;
		-- 如果游标没有结束
		IF NOT done THEN
			-- 根据cid将找到的create_date赋值给ctime变量
			SELECT create_date INTO ctime FROM given_record WHERE orders = 1 AND coupon_Id = cid;
			-- 如果ctime变量加上5分钟小于当前时间则进行更新操作
			IF DATE_ADD(ctime, INTERVAL 5 MINUTE)< NOW() THEN
				UPDATE member_coupon SET STATUS = 1 WHERE STATUS = 3 AND id = cid;
				UPDATE given_record SET orders = 3 WHERE orders = 1 AND coupon_Id = cid;
			END IF;
		END IF;	
	UNTIL done END REPEAT;
	CLOSE cur;
    END$$

DELIMITER ;

4、其中使用到了游标 CURSOR 和 循环 REPEAT，我们下节进行描述。

三、结束语：这样的话就可以通过事件每隔30分钟调用存储过程来处理若5分钟未被领取则归还赠送者业务！

欢迎指正。