-- 连接mysql
mysql -h 192.168.102.168 -u root -proot webapm

-- 执行文件
\. event.sql

use webapm;

--创建存储过程
DELIMITER $$
CREATE PROCEDURE `p_del_slow`(IN `date_inter` INT)
BEGIN
    DELETE FROM slow_duration_new WHERE (TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(create_time/1000)))>=date_inter;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `p_del_error`(IN `date_inter` INT)
BEGIN
    DELETE FROM error_report WHERE (TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(create_time/1000)))>=date_inter;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `p_del_perf`(IN `date_inter` INT)
BEGIN
    DELETE FROM perf_report WHERE (TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(create_time/1000)))>=date_inter;
END$$
DELIMITER ;

-- test
DELIMITER $$
CREATE PROCEDURE `i_sert`(IN `date_inter` INT)
BEGIN
    INSERT INTO slow_duration(route) values('huangtao');
END$$
DELIMITER ;

-- 删除存储过程
drop procedure if exists p_del_slow;
drop procedure if exists p_del_error;
drop procedure if exists p_del_perf;

-- 列出所有存储过程
select specific_name from mysql.proc;

-- 查看某一个存储过程的具体内容
select body from mysql.proc where specific_name = 'p_del_slow';

-- 调用存储过程
call p_del_slow();

-- 查看定时配置开启
show variables like '%event_sche%';
set global event_scheduler=1;

-- 创建定时任务
CREATE EVENT `e_del_slow_route`
ON SCHEDULE EVERY 1 DAY STARTS '2018-2-1 0:0:0'
ON COMPLETION PRESERVE DISABLE
DO CALL p_del_slow (7);

CREATE EVENT `e_del_error_report`
ON SCHEDULE EVERY 1 DAY STARTS '2018-2-1 0:0:0'
ON COMPLETION PRESERVE DISABLE
DO CALL p_del_error (7);

CREATE EVENT `e_del_perf_report`
ON SCHEDULE EVERY 1 DAY STARTS '2018-5-24 0:0:0'
ON COMPLETION PRESERVE DISABLE
DO CALL p_del_perf (7);

-- test
CREATE EVENT `i_second`
ON SCHEDULE EVERY 1 SECOND
ON COMPLETION PRESERVE DISABLE
DO CALL i_sert (7);

-- 数目
select count(*) from slow_duration;

--查看本机所有的事件
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;

--开启定时任务
alter event e_del_slow_route on completion preserve enable;
alter event e_del_error_report on completion preserve enable;
alter event e_del_perf_report on completion preserve enable;

--关闭定时任务
alter event e_del_slow_route on completion preserve disable;
alter event e_del_error_report on completion preserve disable;
alter event e_del_perf_report on completion preserve disable;

--删除定时任务
drop event if exists e_del_slow_route;
drop event if exists e_del_error_report;
drop event if exists e_del_perf_report;

ALTER TABLE slow_duration ADD useragent varchar(200);

-- http://jiyiren.github.io/2016/03/27/Mysql_schedule/