mysql 存储规划_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;