mysql 定时更新表字段列的值状态(存储过程+定时任务)
时间:2016-05-07 14:50 来源:linux.it.net.cn 作者:IT
现在又两个表tcm,doc；
db_count:
id count
1 220
2 300
3 220
4 222
5 240
db_date:
id t_date count
1 2012-09-10 17:04:07 220
2 2012-09-11 15:04:07 224
3 2012-08-20 11:04:07 225
4 2012-02-11 17:04:07 300
5 2012-09-11 10:04:07 500
6 2012-09-12 17:04:07 300
---存储过程变更db_count表count值为0；
DELIMITER //
CREATE PROCEDURE update_count()
BEGIN
DECLARE m date;
DECLARE y CURSOR for select t_date from db_date;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET k=1;
OPEN y;
FETCH y INTO m;
IF SYSDATE()>m THEN
UPDATE db_date t,db_count g SET g.count=0 WHERE t.id=g.id;
END IF;
CLOSE y;
END //
DELIMITER;
----事件，事件间隔为1秒，进行执行该事件
DELIMITER //
CREATE EVENT my_count
ON SCHEDULE EVERY 1 second do
begin
call update_count();
end //
DELIMITER;
select * from db_count;
(责任编辑：IT)
现在又两个表tcm,doc； db_count: id count 1 220 2 300 3 220 4 222 5 240 db_date: id t_date count 1 2012-09-10 17:04:07 220 2 2012-09-11 15:04:07 224 3 2012-08-20 11:04:07 225 4 2012-02-11 17:04:07 300 5 2012-09-11 10:04:07 500 6 2012-09-12 17:04:07 300 ---存储过程变更db_count表count值为0； DELIMITER // CREATE PROCEDURE update_count() BEGIN DECLARE m date; DECLARE y CURSOR for select t_date from db_date; declare CONTINUE HANDLER FOR SQLSTATE '02000' SET k=1; OPEN y; FETCH y INTO m; IF SYSDATE()>m THEN UPDATE db_date t,db_count g SET g.count=0 WHERE t.id=g.id; END IF; CLOSE y; END // DELIMITER; ----事件，事件间隔为1秒，进行执行该事件 DELIMITER // CREATE EVENT my_count ON SCHEDULE EVERY 1 second do begin call update_count(); end // DELIMITER; select * from db_count; (责任编辑：IT) |