1.首先,需要设置mysql数据库的event_sheduler=on事件:
show variables like 'event_scheduler';
set GLOBAL event_scheduler=on;
2.写一个面向过程:
BEGIN
#Routine body goes here... DECLARE isend INT DEFAULT 0; DECLARE custid INT; DECLARE custcode VARCHAR(20); DECLARE custstatus INT; DECLARE cur CURSOR FOR SELECT t.cust_id,cust_code,t.cust_status FROM cust t WHERE cust_end_time<=SYSDATE(); DECLARE CONTINUE HANDLER FOR NOT FOUND SET isend=1; OPEN cur; FETCH cur INTO custid,custcode,custstatus; WHILE isend!=1 DO UPDATE cust SET cust_status=1 WHERE cust_id=custid AND cust_code=custcode; FETCH cur INTO custid,custcode,custstatus; END WHILE; CLOSE cur;END
保存为:change_cust_status;
3.新建一个事件:
定义里面执行 call change_cust_status;
在计划里设置时间;
就是这么简单;