萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> MySql如何執行定時任務

MySql如何執行定時任務

   Mysql屬於中小型數據庫系統,它的事件調度器Event Scheduler是在mysql 5.1才開始引入事件調度器是在 MySQL 5.1 中新增的另一個特色功能,可以作為定時任務調度器,取代部分原先只能用操作系統任務調度器才能完成的定時功能。事件調度器是定時觸發執行的,在這個角度上也可以稱作是"臨時的觸發器"。觸發器只是針對某個表產生的事件執行一些語句,而事件調度器則是在某一個(間隔)時間執行一些語句。事件是由一個特定的線程來管理的,也就是所謂的"事件調度器"。

  代碼:

  /*開啟event_scheduler,也可以設置為on*/

  set global event_scheduler =1;

  /*創建簡單的任務,每分鐘執行一次,從一個表取數據插入到另一個表,也可以定時取,可以參看下面文檔,此處為簡單例子*/

  CREATE EVENT MyEvent

  ON SCHEDULE EVERY 1 MINUTE

  DO

  INSERT INTO list_test (字段1,字段1,字段1,字段1,字段1) select 字段1,字段1,字段1,字段1,字段1 from 表2 where id = 510;

  /*每天固定時間執行*/

  CREATE EVENT EVENT_ADD_FOR20_ENOUGH

  ON SCHEDULE EVERY 1 DAY

  STARTS TIMESTAMP '2009-12-18 02:58:00'/×MYSQL注意時區設置,默認非中國時區×/

  DO

  SQL語句

  下載:MySQL-Events-CN.rar

  再加兩段吧

  代碼:

  DELIMITER $$

  DROP PROCEDURE IF EXISTS `mystock`.`TEST_KKK`$$

  CREATE PROCEDURE `mystock`.`TEST_KKK`()

  /*LANGUAGE SQL

  | [NOT] DETERMINISTIC

  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

  | COMMENT 'string'*/

  BEGIN

  DECLARE NUM INTEGER DEFAULT 0;

  DECLARE $A INT;

  SELECT (20-COUNT(STOCK_CODE)) as number into NUM FROM get_stock_list WHERE FLAG = 0 AND STIME = curdate();

  IF NUM>0 THEN

  PREPARE STMP FROM 'INSERT INTO get_stock_list (stock_code,stock_name,close,raises,stime) select stock_code,stock_name,close,result1,selectd from choice_stock where stock_code not in (select stock_code from get_stock_list where stime = curdate() and flag = 0) and selectd = curdate() and selectd = curdate() order by id limit ?' ;

  SET @A = NUM;

  EXECUTE STMP USING @A;

  END IF;

  END$$

  DELIMITER ;

  代碼:

  DELIMITER $$

  DROP FUNCTION IF EXISTS `mystock`.`FUNCTION_ADD_STOCK_FOR20`$$

  CREATE DEFINER=`root`@`localhost` FUNCTION `FUNCTION_ADD_STOCK_FOR20`() RETURNS int(11)

  BEGIN

  DECLARE NUM,ANOTHER INTEGER;

  SELECT COUNT(STOCK_CODE) as number into NUM FROM get_stock_list WHERE FLAG = 0 AND STIME = curdate();

  if(NUM<20)

  THEN

  SET ANOTHER = 20 - NUM;

  INSERT INTO get_stock_list (stock_code,stock_name,close,raises,stime) select stock_code,stock_name,close,result1,selectd from choice_stock where stock_code not in (select stock_code from get_stock_list where stime = curdate() and flag = 0) order by id LIMIT 10;

  END IF;

  RETURN NUM;

  END$$

  DELIMITER ;

copyright © 萬盛學電腦網 all rights reserved