定时器中调用存储过程

最新推荐文章于 2021-02-24 20:21:08 发布

ZeusLuna

最新推荐文章于 2021-02-24 20:21:08 发布

阅读量1.1k

点赞数 1

分类专栏： MySql 文章标签： MySql

本文链接：https://blog.csdn.net/leipeng321123/article/details/49685569

版权

MySql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

存储过程：

 DELIMITER //
DROP PROCEDURE if exists log_system;
CREATE PROCEDURE log_system (in u_n INT,u_d INT,s_n INT,s_d INT)
BEGIN
     SET u_d = (SELECT userloggingDate FROM logging_prameter);
     DELETE  FROM user_logging  WHERE time <= (select date_add(sysdate(),interval - u_d day) as yestoday);
     IF (SELECT COUNT(*) FROM user_logging) > (SELECT userLoggingNumber FROM logging_prameter) THEN
         SET u_n = (select COUNT(*) FROM user_logging)-(SELECT userLoggingNumber FROM logging_prameter);
         DELETE FROM user_logging ORDER BY time LIMIT u_n;
     END IF;
    
    SET s_d = (SELECT sysLoggingDate FROM logging_prameter);
    DELETE  FROM sys_logging  WHERE time <= (select date_add(sysdate(),interval - s_d day) as yestoday);
    IF (SELECT COUNT(*) FROM sys_logging) > (SELECT sysLoggingNumber FROM logging_prameter) THEN
         SET s_n = (select COUNT(*) FROM sys_logging)-(SELECT sysLoggingNumber FROM logging_prameter);
         DELETE FROM sys_logging ORDER BY time LIMIT s_n;
    END IF;
END;
//DELIMITER

定时器：

  DELIMITER //
    SET GLOBAL event_scheduler = ON;  
    DROP EVENT IF EXISTS `eventSysUserLogging`;  
    CREATE    EVENT IF NOT EXISTS `eventSysUserLogging`  
       
    ON SCHEDULE  
    EVERY 1 DAY   
     STARTS TIMESTAMP(CURRENT_DATE,'09:00:00')  
     ON COMPLETION PRESERVE ENABLE  
    DO  
        BEGIN  
            CALL log_system(@u_n,@u_n,@s_n,@s_d);
        END;  
// DELIMITER