mysql使用存储过程执行定时任务

https://blog.csdn.net/weixin_36931308/article/details/77624146

 https://www.cnblogs.com/woshimrf/p/5665236.html

 https://blog.csdn.net/nextop88/article/details/78740141

创建存储过程

DELIMITER //
CREATE PROCEDURE IpAuth()
BEGIN
update data_view_ip_auth set run_state = 2 where end_time < now();
END//
DELIMITER ;

创建事件

CREATE   EVENT IpAuth
ON SCHEDULE EVERY 1 DAY STARTS '2021-07-20 00:00:00'
ON COMPLETION PRESERVE
ENABLE
DO CALL IpAuth();

查看定时器是否开启

SHOW VARIABLES LIKE 'event_scheduler'

 如果不是ON,需要开启定时器

set GLOBAL event_scheduler = 1;

开启和关闭事件




开启

ALTER EVENT test_sche_event ENABLE;

关闭

ALTER EVENT test_sche_event DISABLE;

 

 

CREATE DEFINER=`intest`@`%` PROCEDURE `setUserName`()
BEGIN  
  
    DECLARE nRetRowCount int;  
  
    SELECT count(*) INTO nRetRowCount  
    FROM rule_label_info  
    WHERE label_name like '%sb%';  
  
    IF nRetRowCount = 0 THEN 
        insert into rule_label_info
(rule_label_type,
label_name,
rule_info,
create_user,
flag) values (
'车速',
'sb',
'温度等于0与温度小于40',
2,
1
) ;
    END IF;  
  
END