主页 / dba / 问题 / 80049
Accepted
Masoud
Asked: 2014-09-20 00:29:22 +0800 CST

创建在每个月底执行的事件

  • 772

我需要在 mysql 中创建一个事件，该事件在每个月底晚上 11 点运行。我尝试创建一个事件，该事件使用以下代码创建另一个事件：

DELIMITER | 
CREATE EVENT `2014-10-31_CALL_ISRSP` ON SCHEDULE AT '2014-10-31 23:50:00' 
DO 
BEGIN 
call myStoredProcedure(); 
set @creatNextEvnt := Concat("CREATE EVENT ",LAST_DAY( NOW() + INTERVAL 1 MONTH ),"_CALL_ISRSP ON SCHEDULE AT '",LAST_DAY( NOW() + INTERVAL 1 MONTH )," 23:50:00' DO BEGIN call IncidentsSummaryRecorder(); set @creatNextEvnt := Concat(\"CREATE EVENT \",LAST_DAY( NOW() + INTERVAL 1 MONTH ),\"_CALL_ISRSP ON SCHEDULE AT '\",LAST_DAY( NOW() + INTERVAL 1 MONTH ),\" 23:50:00'\"); PREPARE cer FROM @creatNextEvnt; EXECUTE cer; DEALLOCATE PREPARE cer; set @delLastEvent := Concat(\"DROP IF EXISTS EVENT \",LAST_DAY(NOW()),\"_CALL_ISRSP\");  PREPARE del FROM @delLastEvent; EXECUTE del; DEALLOCATE PREPARE del; END;");
select @creatNextEvnt;
PREPARE cer FROM @creatNextEvnt; 
EXECUTE cer; 
DEALLOCATE PREPARE cer; 
set @delLastEvent := Concat("DROP IF EXISTS EVENT ",LAST_DAY(NOW()),"_CALL_ISRSP");  
PREPARE delFROM @delLastEvent; 
EXECUTE del; 
DEALLOCATE PREPARE del; 
END|

但这给了我一个错误，即准备好的语句还不支持这种类型的命令。

我尝试将我的 SQL 字符串存储到一个文件中并在同一个事件中执行它们，但我不知道如何执行我创建的文件。有什么帮助吗？

mysql mysql-event

2 个回答

  1. Best Answer

    您永远不必自动删除和创建新事件。

    您应该使事件可重复：

    • 每分钟，检查日期和时间
    • 如果该月最后一天的小时是 23，分钟是 50，请致电IncidentsSummaryRecorder();

    这是那个代码

    DELIMITER $$

CREATE EVENT ISRSP
ON SCHEDULE
    EVERY 1 MINUTE
    STARTS '2014-10-31 23:50:00'
    ON COMPLETION PRESERVE
DO
BEGIN
    DECLARE rightnow DATETIME;
    DECLARE hh,mm TINYINT;

    SET rightnow = NOW();
    SET hh = HOUR(rightnow);
    SET mm = MINUTE(rightnow);

    IF DATE(rightnow) = LAST_DAY(DATE(rightnow)) THEN
        IF hh = 23 THEN
            IF mm = 50 THEN
                CALL IncidentsSummaryRecorder();
            END IF;
        END IF;
    END IF;

END $$

DELIMITER ;

    试试看 ！！！

    • 2

  2. 在 BEGIN 和 END 之间，使用以下代码：

    IF DAY(NOW()) = DAY(LAST_DAY(NOW())) THEN
IF HOUR(NOW()) = 23 THEN
IF MINUTE(NOW()) = 50 THEN
CALL IncidentsSummaryRecorder();
END IF;
END IF;
END IF;
    • 0

相关问题