1、修改数据库参数,开启作业调度器
修改C:\ProgramData\MySQL\MySQL Server 5.6下的my.ini文件,增加一行:event_scheduler=ON
2、新建存储过程
增加生成实时随机数的函数
CREATE FUNCTION rand_num (
start_num INTEGER,
end_num INTEGER
) RETURNS INTEGER
BEGIN
RETURN FLOOR(start_num + RAND() * (end_num - start_num + 1));
END;
存储过程:
delimiter //
DROP PROCEDURE IF EXISTS p_insertCollect2 //
CREATE PROCEDURE p_insertCollect2
(IN inparmsTime datetime)
BEGIN
DECLARE tScore FLOAT;
set tScore=rand_num(19000, 22000);
insert into es_collect_data (collectItemCode,collectTime,collectValue,couValue) values ('D001',inparmsTime,tScore,tScore);
set tScore=rand_num(18000, 21000);
insert into es_collect_data (collectItemCode,collectTime,collectValue,couValue) values ('D002',inparmsTime,tScore,tScore);
set tScore=rand_num(7000, 9000);
insert into es_collect_data (collectItemCode,collectTime,collectValue,couValue) values ('FT0001',inparmsTime,tScore,tScore);
set tScore=rand_num(80, 110);
insert into es_collect_data (collectItemCode,collectTime,collectValue,couValue) values ('FT301',inparmsTime,tScore,tScore);
END
//
delimiter ;
测试存储过程:
CALL p_insertCollect2(now());
3、新建定时作业,从某时间开始,定时多长时间执行一次
DELIMITER //
CREATE EVENT e_insertCollect2
ON SCHEDULE EVERY 15 MINUTE STARTS TIMESTAMP '2018-08-08 00:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
CALL p_insertCollect2(now());
END//