因公司项目中有个赠送优惠券功能,此功能需求为:赠送出去的优惠券若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分钟未被领取则归还赠送者业务!
欢迎指正。