CREATE DEFINER=`root`@`%` PROCEDURE `auto_update_free_num`(
-- out _start_time VARCHAR(50),#
-- out _now_time TIMESTAMP,
-- out _end_time TIMESTAMP
)
BEGIN
DECLARE _active_time_id int(11);
DECLARE _start_time TIMESTAMP;
DECLARE _now_time TIMESTAMP;
DECLARE _end_time TIMESTAMP;
#Routine body goes here...
#SET start_time=(SELECT start_day FROM eb_sxxxx_time where status=1 and loss_status=0 ORDER BY active_time_id DESC LIMIT 1);
#SET end_time=(SELECT end_day FROM eb_sxxxx_time where status=1 and loss_status=0 ORDER BY active_time_id DESC LIMIT 1);
#获取时间段，活动开始为用户添加活动次数，并做标记，轮询为新用户增加活动次数，并做标记，活动结束关掉时间段开关，重置用户标记，重置用户次数
START TRANSACTION;
select start_day,end_day,active_time_id into _start_time,_end_time,_active_time_id from eb_sxxxx_time where status=1 and loss_status=0 ORDER BY active_time_id DESC LIMIT 1;
SET _now_time=CURDATE();
IF _now_time=_start_time THEN #活动当天更新
update xx_user SET member_free_num_lock=1,member_free_num=1 WHERE member_free_num_lock=0;
END IF;
--
IF _now_time > _start_time AND _now_time < _end_time THEN #活动期间更新
update xx_user SET member_free_num_lock=1,member_free_num=1 WHERE member_free_num_lock=0;
END IF;
--
IF _now_time = _end_time THEN #活动结束
update eb_sxxxx_time SET loss_status=1 WHERE active_time_id=_active_time_id;#更新时间段表;
update xx_user SET member_free_num_lock=0,member_free_num=0;#更新user表
END IF;
COMMIT;
#if()
END
写完后发现定时任务没执行，发现mysql的EVENT 支持没开，开启后跑的非常6 查询开启代码
Select @@event_scheduler;
SET GLOBAL event_scheduler = ON;
