mysql 定时更新_mysql 定时更新表字段列的值状态（转载）

现在又两个表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;