mysql event

最新推荐文章于 2024-05-08 19:10:18 发布

伴生伴熟

最新推荐文章于 2024-05-08 19:10:18 发布

阅读量332

点赞数

分类专栏： mysql 文章标签： mysql event 事件

本文链接：https://blog.youkuaiyun.com/chinacmt/article/details/53127986

版权

mysql 专栏收录该内容

12 篇文章

订阅专栏

1.查看事件调度是否开启

mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

2.开启事件调度

mysql> set global event_scheduler = on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

3.事件调度示例

DELIMITER $$

CREATE EVENT `event_test`
    ON SCHEDULE EVERY 1 DAY STARTS '2016-10-01 01:00:00' ENDS '2026-10-01 01:00:00' 
    ON COMPLETION PRESERVE ENABLE
    DO BEGIN
      CALL sp_data_move_history();
  END$$

DELIMITER ;

4.事件调度参数：

DELIMITER $$

-- SET GLOBAL event_scheduler = ON$$     -- required for event to execute but not create    

CREATE  /*[DEFINER = { user | CURRENT_USER }]*/ EVENT `blog`.`event_test`

ON SCHEDULE
     /* uncomment the example below you want to use */

    -- scheduleexample 1: run once

       --  AT 'YYYY-MM-DD HH:MM.SS'/CURRENT_TIMESTAMP { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] }

    -- scheduleexample 2: run at intervals forever after creation

       -- EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]

    -- scheduleexample 3: specified start time, end time and interval for execution
       /*EVERY 1  [HOUR|MONTH|WEEK|DAY|MINUTE|...]

       STARTS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1[HOUR|MONTH|WEEK|DAY|MINUTE|...] }

       ENDS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] } */

/*[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']*/

DO
    BEGIN
        (sql_statements)
    END$$

DELIMITER ;