MySQL scheduled task operation steps and code demonstration

The following articles mainly introduce the actual operation steps of MySQL scheduled tasks (scheduled execution) and the actual application code for implementing MySQL scheduled tasks, the following describes the detailed operation steps. I hope this will help you in this regard.

MySQL is a small and medium database system. Its Event Scheduler, Event sched, is introduced only in MySQL 5.1, which is another feature added in MySQL 5.1, it can be used as a MySQL scheduled task scheduler to replace 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 set it to 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
  
  
Insert 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;
  
  
/* Execute the task at a fixed time every day */
  
  
Create event EVENT_ADD_FOR20_ENOUGH
  
  
On schedule every 1 DAY
  
  
Starts timestamp '2017-12-18 02:58:00 '/× MySQL pay attention to the time zone settings. The default value is not the China Time Zone ×/
  
  
DO
 
 

SQL statement

 
 

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

Add two more sections.

The code used in the actual operations of the MySQL scheduled task is as follows:

 
 

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

The above content is an introduction to MySQL scheduled tasks. I hope you will get some benefits.