MySQL Event歷史記錄

USE db1;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` EVENT `event_test1` 
ON SCHEDULE EVERY 1 MINUTE STARTS '2019-01-01 00:00:00' 
ON COMPLETION PRESERVE ENABLE DO 
BEGIN  
    DECLARE r_code CHAR(5) DEFAULT '00000';  
    DECLARE r_msg TEXT;  
    DECLARE v_error INT;  
    DECLARE v_start_time DATETIME(3) DEFAULT NOW(3);
    DECLARE v_event_gid VARCHAR(36) DEFAULT UPPER(REPLACE(UUID(),'-',''));  
    
    /*修改成實際的event名*/
    INSERT INTO mysql.udf_event_history (db_name, event_name, start_time, event_gid)  
    VALUES(DATABASE(), 'event_test1', v_start_time, v_event_gid);    
      
    BEGIN     
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION    
        BEGIN  
            SET  v_error = 1;  
            GET DIAGNOSTICS CONDITION 1 r_code = RETURNED_SQLSTATE, r_msg = MESSAGE_TEXT;  
        END;  
        /*實際要執行語句或存儲過程，等*/
        CALL db1.usp_1();
    END;  
      
    UPDATE mysql.udf_event_history 
    SET end_time = NOW(3), is_success = ISNULL(v_error), duration = TIMESTAMPDIFF(microsecond,start_time, NOW(3)) / 1000000, 
    error_msg = CONCAT('error = ', r_code,', message = ', r_msg)
    WHERE event_gid = v_event_gid;  
      
END$$  
DELIMITER ;