Mysql计时器定时更新功能

最新推荐文章于 2024-07-10 11:32:08 发布

心脏dance

最新推荐文章于 2024-07-10 11:32:08 发布

阅读量511

点赞数 2

分类专栏： Mysql 文章标签： Mysql SQL

本文链接：https://blog.csdn.net/lgz0921/article/details/106224170

版权

Mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

create table Status(

statusId int not null auto_increment COMMENT '状态id',
statusName varchar(50) not null COMMENT '状态名称',
PRIMARY KEY ( `statusId` )

)ENGINE=InnoDB DEFAULT CHARSET=utf8;



create table Activity(

actId int not null auto_increment COMMENT '活动id',
actName varchar(100) not null COMMENT '活动名称',
actImg varchar(255) not null COMMENT '活动海报',
actStartDate datetime not null COMMENT '活动开始日期',
actDeadLine datetime not null COMMENT '活动截止日期',
actRegDate datetime not null COMMENT '报名开始时间',
actRegDeadline datetime not null COMMENT '报名截止时间',
actPlace varchar(100) not null COMMENT '地点',
actNumber int not null  COMMENT'人数限制',
actRequire varchar(100) not null COMMENT '报名要求',
actSlogan varchar(100) not null COMMENT '宣传口号',
actScore int not null COMMENT '活动积分',
actProfile text not null COMMENT '活动简介',
actStatus int not null COMMENT '活动状态',
actTag int not null COMMENT '标签',
actIsCheck int not null COMMENT '活动是否通过检验',
actIsCancel int not null COMMENT '活动取消',
actPromoter int not null COMMENT '活动发起人Id',
PRIMARY KEY ( `actId` ),
FOREIGN KEY (actPromoter) REFERENCES User (userId)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 对这两个表进行瞎搞（主要针对活动表）

-- 查看是否开启定时器
SHOW VARIABLES LIKE 'event_scheduler';
-- 开启定时器 0：off 1：on
SET GLOBAL event_scheduler = 1; 


-- 开启event_scheduler SQL指令
SET GLOBAL event_scheduler = ON;  
SET @@global.event_scheduler = ON;  
SET GLOBAL event_scheduler = 1;  
SET @@global.event_scheduler = 1;


-- 定义存储过程
DELIMITER |  
DROP PROCEDURE IF EXISTS update_remind_status |  
CREATE PROCEDURE update_remind_status()  
BEGIN  
    IF exists (select actId from activity where SYSDATE()<actRegDate) THEN  
            update activity set `actStatus`=1 where SYSDATE()<remind_time and `actStatus`!=1;  
    END IF; 
		IF exists (select actId from activity where SYSDATE()>=actRegDate and  SYSDATE()<=actRegDeadline) THEN  
            update activity set `actStatus`=2 where SYSDATE()>=actRegDate and  SYSDATE()<=actRegDeadline and `actStatus`!=2;  
    END IF;  
		IF exists (select actId from activity where SYSDATE()>actRegDeadline and SYSDATE()<actStartDate) THEN  
            update activity set `actStatus`=3 where SYSDATE()>actRegDeadline and SYSDATE()<actStartDate and `actStatus`!=3;  
    END IF;  
		IF exists (select actId from activity where SYSDATE()>=actStartDate and SYSDATE()<=actDeadLine) THEN  
            update activity set `actStatus`=4 where SYSDATE()>=actStartDate and SYSDATE()<=actDeadLine and `actStatus`!=4;  
    END IF;  
		IF exists (select actId from activity where SYSDATE()>actDeadLine) THEN  
            update activity set `actStatus`=5 where SYSDATE()>actDeadLine and `actStatus`!=5;  
    END IF;  
END   
 |  
DELIMITER;  


--创建定时器，每间隔一秒调用一次存储过程。
DELIMITER //  
CREATE EVENT  event_remind_status  
ON SCHEDULE EVERY 1 second  do  
begin  
call update_remind_status();  
end //  
DELIMITER;  

-- 启动定时器
ALTER EVENT event_remind_status ON   
COMPLETION PRESERVE ENABLE;