MySQL EVENT

2019独角兽企业重金招聘Python工程师标准>>>

MySQL版本5.1及以上.

写个定时器, 做点小事情,很方便.

Concept

Event - mysql 自带的定时器,任务调度;

详细定义看官方文档: Working with MySQL Scheduled Event

Common

-- common
SET GLOBAL event_scheduler = ON; -- 设置开启
SHOW VARIABLES WHERE VARIABLE_NAME = 'event_scheduler'; -- 查看是否开启

SHOW PROCESSLIST;

-- 查看定时任务event(事件)，可以查看本机所有的事件
SELECT event_name, event_definition, interval_value, interval_field, status FROM information_schema.EVENTS;

alter event login_flag_set_daily disable; -- 停止某event
alter event login_flag_set_daily enable; -- 开启某event
drop event if exists TEST_INSERT01; -- 删除event

 

Event log table

CREATE TABLE `ms_events_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `event_name` varchar(50) COMMENT 'event name',
  `content` varchar(500) DEFAULT NULL COMMENT '内容',
  `type` INT(4) DEFAULT NULL COMMENT 'bak',
  `flag` char(2) DEFAULT 0 COMMENT '启用标志 0-启用 1-停用',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `create_date` timestamp DEFAULT NOW(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='events log表';

Event example

-- example --------------------------- https://bbs.csdn.net/topics/392233680
delimiter |

CREATE EVENT e_daily
    ON SCHEDULE EVERY 1 DAY
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
        INSERT INTO site_activity.totals (time, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END |

delimiter ;
-- ------------------------

-- EXAMPLE --------
delimiter |

CREATE EVENT `login_flag_set_daily` 
	ON SCHEDULE EVERY 1 DAY STARTS '2019-05-07 03:00:00' -- 每天3点执行
    COMMENT '设置登录用户的登录状态'
	DO
		BEGIN
			update user u 
			where u.state = 2 and u.frozen_state = 0 
				and u.id = 991;
            
            insert into ms_events_log (event_name, content) values ('login_flag_set_daily', '更新用户登录状态');
		END |

delimiter ; 

Event to call procedure

-- create procedure 
DELIMITER //

 drop procedure if exists proc_monitor_job_sa;
 CREATE PROCEDURE proc_monitor_job_sa()
   BEGIN
	DECLARE failed_records INT;
    SELECT count(1) into failed_records from order_list l where l.send_flag = 3;

    if failed_records > 0 then
		-- send email
		-- insert into message_email ......;
	end if;
	-- log
    insert into ms_events_log (event_name, content) values ('monitor_job_sa', 'sa 监控');
   END //

DELIMITER ;


-- call monitor_job_sap();

-- event example call procedure
CREATE EVENT `event_monitor_job_sa`
	ON SCHEDULE EVERY 1 DAY STARTS '2019-05-07 12:00:00'
    COMMENT 'SA job monitor'
	DO call proc_monitor_job_sa();

 

转载于:https://my.oschina.net/joryqiao/blog/3046660