mysql存储过程和执行计划案例

开启event_scheduler指令：

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;
关闭event_scheduler指令：

SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;

 

创建临时表

CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建存储过程

create procedure sp_name()
begin
DECLARE a INT DEFAULT 10;  -- 定义变量
DECLARE b INT DEFAULT 0;   -- 定义变量
set b = (select IFNULL(max(id),0) FROM student);  -- 将查询的结果变成临时变量
set a = b + 10;  

while b < a DO    --  执行循环
set b = b + 1;
INSERT INTO student (id,name) values (b,floor(rand()*100));
end while;
end

 

DROP procedure sp_name;

-- 创建执行计划啊

CREATE EVENT IF NOT EXISTS sysYipsEvent
ON SCHEDULE EVERY 2 MINUTE
ON COMPLETION PRESERVE
DO CALL sp_name();

drop event sysYipsEvent;