T N T

美食博主

0%

$8 事件相关操作

Posted on Edited on In

本节记录事件相关操作

查看事件

  1. SELECT @@EVENT_SCHEDULER 查看事件是否开启 
    1
    2
    3
    4
    5
    6
    7
    (root@localhost) [book]> select @@event_scheduler;
    +-------------------+
    | @@event_scheduler |
    +-------------------+
    | ON                |
    +-------------------+
    1 row in set (0.00 sec)

创建事件

  1. SET GLOBAL EVENT_SCHEDULER=1 开启事件 

    1
    2
    (root@localhost) [book]> set global event_scheduler=1;
    Query OK, 0 rows affected (0.01 sec)

  2. CREATE EVENT event... 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    (root@localhost) [book]> create table aaa(
        -> timeline timestamp default current_timestamp);
    Query OK, 0 rows affected (0.00 sec)

    (root@localhost) [book]> desc aaa;
    +----------+-----------+------+-----+-------------------+-------------------+
    | Field    | Type      | Null | Key | Default           | Extra             |
    +----------+-----------+------+-----+-------------------+-------------------+
    | timeline | timestamp | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
    +----------+-----------+------+-----+-------------------+-------------------+
    1 row in set (0.00 sec)

    (root@localhost) [book]> insert into aaa values();
    Query OK, 1 row affected (0.00 sec)

    (root@localhost) [book]> select * from aaa;
    +---------------------+
    | timeline            |
    +---------------------+
    | 2020-02-10 15:54:52 |
    +---------------------+
    1 row in set (0.00 sec)

    (root@localhost) [book]> create event e_test_insert         创建事件
        -> on schedule every 2 second
        -> do
        -> insert into aaa values();
    Query OK, 0 rows affected (0.01 sec)

    (root@localhost) [book]> select * from aaa;
    +---------------------+
    | timeline            |
    +---------------------+
    | 2020-02-10 15:54:52 |
    | 2020-02-10 15:55:57 |
    | 2020-02-10 15:55:59 |
    | 2020-02-10 15:56:01 |
    +---------------------+
    4 rows in set (0.00 sec)

修改事件

  1. ALTER EVENT event DISABLE 停用事件 

    1
    2
    (root@localhost) [book]> alter event e_test_delete disable;     
    Query OK, 0 rows affected (0.01 sec)

  2. ALTER EVENT event ENABLE 事件 

    1
    2
    (root@localhost) [book]> alter event e_test_delete enable;
    Query OK, 0 rows affected (0.01 sec)

删除事件

  1. DROP EVENT event 
    1
    2
    (root@localhost) [book]> drop event e_test_delete;
    Query OK, 0 rows affected (0.01 sec)