首页 > 代码库 > mysql定时脚本(event),类似oracle的job

mysql定时脚本(event),类似oracle的job

mysql定时脚本(event),类似oracle的job
 
我有2张表:tb_push_data 和 tb_push_data_log
现在需要每隔一段时间将tb_push_data 符合条件的
数据备份到表 tb_push_data_log
  www.2cto.com  
--------------------------------------------------------------------------
 
一、创建存储过程
 
DELIMITER $$
 
USE `push_server_db`$$
 
DROP PROCEDURE IF EXISTS `proc_tb_push_data_backup`$$
 
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_tb_push_data_backup`()
BEGIN
    START TRANSACTION;
    
    INSERT INTO tb_push_data_log(id,created_date,data_id,gps,have_image,message,recv_name,
recv_username,send_name,send_username,STATUS,tool,updated_date,uri,image_path,log_time)
    SELECT id,created_date,data_id,gps,have_image,message,recv_name,recv_username,send_name,
send_username,STATUS,tool,updated_date,uri,image_path,NOW()
    FROM tb_push_data WHERE STATUS = ‘ALREADY_RECEIVE‘;
    DELETE FROM tb_push_data WHERE STATUS = ‘ALREADY_RECEIVE‘;
    
    COMMIT;
    END$$
 
DELIMITER ;
 
二、创建MYSQL事件
 
DELIMITER $$
 
CREATE event event_tb_push_data_backup
 
ON SCHEDULE
 
EVERY 5 MINUTE
 
STARTS NOW()
 
DO
 
BEGIN
 
CALL event_tb_push_data_backup();
 
END;
 
DELIMITER ;
 
三、验证并修改数据库是否开启了事件  www.2cto.com  
 
大家按照例子做完以后,可能发现没有运行event,原因大都是因为
event_scheduler状态没打开
 
查看event_scheduler状态
 
show variables like ‘%sche%‘;
 
修改event_scheduler状态
 
set global event_scheduler=1;

mysql定时脚本(event),类似oracle的job