MySQL 创建存储过程 定时器 实现5秒插入一条测试数据

1.建测试表

drop table if EXISTS dajizu;
create table dajizu
(
id INT(20) not null AUTO_INCREMENT,
time datetime not null,
a double not null,
b double not null,
c double not null,
d double not null,
e double not null,
f double not null,
g double not null,
h double not null,
i double not null,
j double not null,
k double not null,
l double not null,
m double not null,
n double not null,
o double not null,
ap double not null,
bp double not null,
cp double not null,
dp double not null,
ep double not null,
fp double not null,
gp double not null,
hp double not null,
ip double not null,
jp double not null,
kp double not null,
lp double not null,
mp double not null,
np double not null,
op double not null,
primary key (id)
)

select * from dajizu;

2.插入一条测试数据

insert dajizu values(0, SYSDATE(), 42.4, 45.3, -0.03, -0.03, 45.8, 45.2, 8592.7, 8592.3, 0.0, 8592.7, 8592.7, 8594.0, 15.2, 49.3, 12.7, 394.2, 3.438, 18102.9, 16.3, 48.9, 12.5, 1.046, 75.6, 75.7, 76.1, 22.0, 75.5, 12.1, 56.4, 6.5);

3.创建存储过程

CREATE PROCEDURE insert_djz_pro()
BEGIN
 insert dajizu values(0, SYSDATE(), 42.4, 45.3, -0.03, -0.03, 45.8, 45.2, 8592.7, 8592.3, 0.0, 8592.7, 8592.7, 8594.0, 15.2, 49.3, 12.7, 394.2, 3.438, 18102.9, 16.3, 48.9, 12.5, 1.046, 75.6, 75.7, 76.1, 22.0, 75.5, 12.1, 56.4, 6.5);
END;

4.创建定时器

create event if not exists insert_djz_event_job 
on schedule every 5 second 
on completion PRESERVE
do call insert_djz_pro();

5.启动定时器

SET GLOBAL event_scheduler = 1;  -- 启动定时器
-- SET GLOBAL event_scheduler = 0;  -- 停止定时器

6.查看定时器状态

SHOW VARIABLES LIKE '%sche%'; -- 查看定时器状态

event_scheduler ON