Mysql 事件

1. 检查数据库事件是否开启，event_scheduler=ON表示开启

SELECT @@event_scheduler;
SHOW VARIABLES LIKE 'event_scheduler';

2. 开启事件

• 法1：

SET GLOBAL event_scheduler=1;

• 法2：

SET GLOBAL event_scheduler=ON;

• 法3：

在my.cnf中的[mysqld]部分添加 event_scheduler=ON，然后重启mysql。

3. 关闭事件

SET GLOBAL event_scheduler=OFF;

4. 事件语法

CREATE
        [DEFINER = { user | CURRENT_USER }]
        EVENT
        [IF NOT EXISTS]
        event_name
        ON SCHEDULE schedule
        [ON COMPLETION [NOT] PRESERVE]
        [ENABLE | DISABLE | DISABLE ON SLAVE]
        [COMMENT 'comment']
        DO event_body;
    schedule:
        AT timestamp [+ INTERVAL interval] ...
         | EVERY interval
        [STARTS timestamp [+ INTERVAL interval] ...]
        [ENDS timestamp [+ INTERVAL interval] ...]
    interval:
      quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
                  WEEK | SECOND | YEAR_MONTH | DAY_HOUR |
    DAY_MINUTE |DAY_SECOND | HOUR_MINUTE |
    HOUR_SECOND | MINUTE_SECOND}

5. 实例

5.1 创建表

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t1` datetime DEFAULT NULL,
  `id2` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8

5.1 创建事件1：每隔3秒往test表中插入一条数据

CREATE EVENT IF NOT EXISTS e_test_1 ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO test(id,t1) VALUES(NULL,NOW());

5.2 创建事件2：10分钟后清空test表数据

CREATE EVENT IF NOT EXISTS e_test_2
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO TRUNCATE TABLE test;

5.3 创建事件3：在事件中使用存储过程

CREATE EVENT IF NOT EXISTS e_test_3 ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO CALL pro_test();

• 创建存储过程

CREATE PROCEDURE pro_test()
    BEGIN
        INSERT INTO test(id,t1,id2) VALUES(NULL,NOW(),'1000000');
    END