Implementation of MySQL scheduled tasks and its actual application code

The following articles mainly introduce the actual operation process of MySQL scheduled tasks (scheduled execution operations) and the implementation of MySQL scheduled tasks (scheduled execution operations) we will use the description of the Code in the process. The following describes the specific solution and hope it will be helpful for your future study.

MySQL scheduled task (scheduled execution operation) Mysql is a small and medium database system, its Event Scheduler, Event Scheduler, introduced the Event Scheduler only in mysql 5.1. It is another feature added in MySQL 5.1 and can be used as a scheduled task Scheduler, this replaces some timer functions that can only be completed by the operating system task scheduler. The event scheduler is scheduled to trigger execution, which can also be called a "temporary trigger ". The trigger only executes some statements for the events generated by a table, while the event scheduler executes some statements at a certain interval. Events are managed by a specific thread, that is, the so-called "event scheduler ".

Code

Enable event_scheduler or on

 
 

  
  
set global event_scheduler =1; 
 
 

Create a simple task and execute it once every minute. You can also insert data from one table to another. For more information, see the following document. Here is a simple example.

 
 

  
  
CREATE EVENT MyEvent  
  
  
ON SCHEDULE EVERY 1 MINUTE   
  
  
DO 
 
 

Nsert into list_test (Field 1, Field 1, Field 1, Field 1, Field 1) select Field 1, Field 1, Field 1, Field 1, Field 1, field 1 from table 2 where id = 510;

Daily scheduled execution

 
 

  
  
CREATE EVENT EVENT_ADD_FOR20_ENOUGH  
  
  
ON SCHEDULE EVERY 1 DAY   
  
  
STARTS TIMESTAMP '2009-12-18 02:58:00'/×MYSQL 
 
 

Note the time zone settings. The default value is not the China time zone.

DO

SQL statement

 
 

  
  
/Files/pochonlee/MySQL-Events-CN.rar 
 
 

MySQL scheduled task code

 
 

  
  
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 ;  
 
 

Code

 
 

  
  
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) s
elect 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 ;  
 
 

The above content is an introduction to MySQL scheduled tasks (scheduled execution operations). I hope you will gain some benefits.