1,首先写要实现的插入语句或者函数等例如![](https://img-blog.csdnimg.cn/d824061c81aa4aed86b5e6fee3bf1c16.png)
2,将实现的sql插入储存过程中(dianneng_min_procedure就是存储的名字)
然后
CREATE DEFINER=`root`@`%` PROCEDURE `dianneng_min_procedure`()
BEGIN
//sql放这里
;
END
3,一、首先检查event事件是否开启(下面两个方法均可)
- show variables like ‘event_scheduler’;
- select @@event_scheduler;
4,如果是off则开启
set GLOBAL event_scheduler=1;
-- 或
set GLOBAL event_scheduler=ON;
5.开启定时任务
CREATE EVENT
IF NOT EXISTS my_event
ON SCHEDULE EVERY 5 MINUTE starts TIMESTAMP'2023-06-08 21:15:51';
ON COMPLETION PRESERVE
DO
CALL insert_monitor ();
备注:my_event 事件名字 定时任务的命名:insert_monitor 每5分钟运行一次5 MINUTE
6,查看定时任务是否成功
show events;
最终的在mysql上的流程
//创建转储存事件
CREATE DEFINER = `root` @`%` PROCEDURE `dianneng_min_procedure` () BEGIN
INSERT INTO aek_elect_dianneng_hour ( FXYG, ZXWG, FXWG, TIME ) SELECT
FXYG,
ZXWG,
FXWG,
TIME
FROM
aek_elect_dianneng_min
WHERE
ABS(
TIMESTAMPDIFF(
MINUTE,
time,
NOW()))<= 5
LIMIT 1;
END
//查看定时器是否打开
show variables like 'event_scheduler'
//创建定时任务
CREATE EVENT
IF NOT EXISTS dianneng_min_procedure
ON SCHEDULE EVERY 1 MINUTE starts TIMESTAMP'2023-06-08 21:15:51'
ON COMPLETION PRESERVE
DO
CALL dianneng_min_procedure_event();
————————————————
//展示定时任务
show events
//修改定时任务
ALTER EVENT dianneng_min_procedure
ON SCHEDULE EVERY 1 HOUR STARTS TIMESTAMP'2023-06-08 21:15:51';