萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> 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;

  select * from db_count;

copyright © 萬盛學電腦網 all rights reserved