mysql实现定时执行SQL语句需要用到Event
1.检查event事件是否开启
show variables like '%sche%';
1)如果Value值为OFF,则需要开启。需要超级权限
set global event_scheduler=1;
2.创建存储过程
CREATE PROCEDURE insert_monitor () INSERT INTO `equipment_information` (
device_id,
device_name,
temperature,
humidity,
electric_current,
voltage,
date_time
)
VALUES
(
1,
'主机',
FLOOR(20 +(RAND() * 26)),
FLOOR(1 +(RAND() * 100)),
FLOOR(1 +(RAND() * 10)),
FLOOR(1 +(RAND() * 10)),
SYSDATE()
);
3.创建定时任务
create event if not exists e_monitor
on schedule every 5 second --设置5秒执行一次
on completion preserve
do call insert_monitor(); --执行insert_monitor()存储过程
4.关闭事件任务
alter event e_monitor ON
COMPLETION PRESERVE DISABLE;
5.开启事件任务
alter event e_monitor ON
COMPLETION PRESERVE ENABLE;