mysql的存储过程实现定时任务执行

一 函数

1.1 函数开启

1开启函数：在执行创建函数之前，首先请保证 log_bin_trust_function_creators 参数为 1，即 on 开启状态。

2.开启设置：

设置只存在于当前操作，

set global log_bin_trust_function_creators=1;

show variables like 'log_bin_trust_function_creators';

 3.永久性设置的话：想要永久生效，需要写入到配置文件中：

在 [mysqld] 中加上 log_bin_trust_function_creators=1

1.2 函数的案例

1.创建

DELIMITER $$ 
CREATE FUNCTION rand_num (from_num INT ,to_num INT) 
RETURNS INT(11) 
BEGIN 
DECLARE i INT DEFAULT 0; 
SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ; 
RETURN i; 
END$$

2.调用

select  rand_num(10,30)  from  nongda.tb_org

1.3 删除函数

如果要删除函数：drop function rand_num; 

1.4 函数和存储过程的区别

函数有返回值；存储过程没有返回值

二 存储过程

2.1 存储过程

1.声明存储过程

#执行存储过程，往 dept 表添加随机数据 
DELIMITER $$
CREATE PROCEDURE `insert_dept`( max_num INT ) 
BEGIN 
DECLARE i INT DEFAULT 0; 
SET autocommit = 0; 
REPEAT SET i = i + 1; 
INSERT INTO dept (id,name) VALUES (rand_num(1,500000),'ljf');
UNTIL i = max_num 
END REPEAT; 
COMMIT;
END$$

2.调用存储过程

DELIMITER ;
call insert_dept(50)

3.查看结果：

三 存储过程的定时任务

3.1 查看是否开启事件

1.show variables like '%event_sche%';

2. 开启：

 错误信息是：

Error Code: 1290. The MySQL server is running with the –event-scheduler=DISABLED or –skip-grant-tables option so it cannot execute this statement

skip-grant-tables这个选项用于启动MySQL时跳过权限表的限制，不验证密码。但是如果有了skip-grant-tables，那么event-scheduler是不起作用的，可能也是为了安全性的考虑。

远程到mysql服务器上将my.ini的skip-grant-tables选项去掉，然后确定在my.ini中：

event_scheduler=1

3.修改如下：

4.重启mysql：以管理员身份，进行关闭数据库和启动数据库

 4.再次查看

5. 如果执行完这一句，如果显示access denied;

表示你没有开启定时任务的权限，联系运维给你开通权限。

5.1 在安装的MySQL的目录下，找my.ini文件；新增：skip-grant-tables 

5.2 再重启服务器mysql

5.3  登录mysql，键入mysql –uroot –p；直接回车（Enter）

5.4.输入mysql回车，如果成功，将出现MySQL提示符 >。 
      连接权限数据库： use mysql;  

update user set authentication_string=password('123') where user='root'；

 5.5 退出 quit，停止服务器 

5.6  再到 my.ini文件中注释掉 #skip-grant-tables。

5.7 重启服务器，OK！

3.2  设置定时任务

思想： 先创建存储过程，再定义执行周期

1.设置存储过程

-- 创建存储过程test
CREATE PROCEDURE test_d2 ()
BEGIN
 
DROP TABLE IF EXISTS test_ddd;
/*************************************/
CREATE table test_ddd AS SELECT NOW()
;
END;

2.设置定时事件

 create event if not  exists t_event_name  on schedule at '2022-07-22 08:17:00' on completion preserve
 do call test_d2();

3.结果：

 

 

案例2： 设置每5秒执行一次

DROP EVENT IF EXISTS hh;
create event hh
ON SCHEDULE EVERY 5 SECOND STARTS TIMESTAMP '2022-07-21 01:00:00'
ON COMPLETION PRESERVE
DO
begin
CALL test_d2();
end

 

3.3 常见任务执行周期的设定

# 从2019-10-11开始，每天的00:30:00执行定时任务
ON SCHEDULE EVERY 1 DAY STARTS '2019-10-11 00:30:00'  
 
# 每天的凌晨1点执行定时任务
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
 
# 特定的日期特定的时间点执行定时任务
ON SCHEDULE at '2019-10-10 19:14:10'
 
# 每五分钟执行一次定时任务
ON SCHEDULE EVERY 5 MINUTE STARTS CURDATE() 
 
# 每小时执行一次定时任务
ON SCHEDULE EVERY 1 HOUR STARTS CURDATE()