mysql事件EVENT详解


mysql事件是mysql内部符合一定条件触发的数据库对象

查看mysql的事件服务是否开启:

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

临时开启事件服务(重启mysql之后可能失效配置,会从配置读):

mysql> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected

mysql> SET @@global.event_scheduler = ON;
Query OK, 0 rows affected

永久设定,打开my.cnf(Windows的phpstudy是my.ini)

[mysqld]
event_scheduler=ON

然后重启mysql服务即可。

例子1. 在早上6点钟后,每分钟自动增加auto_increment几十

DELIMITER $$

DROP EVENT IF EXISTS `blog_increment_random`;
CREATE EVENT `blog_increment_random`
ON SCHEDULE EVERY 1 MINUTE
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT '每分钟随机增加自增id的自增值'
DO
BEGIN
SET AUTOCOMMIT = 0;
SET @FINALID = (
    SELECT
        (
            DATE_FORMAT(NOW(), '%i') + FLOOR(1 +(RAND() * 2))
        ) % 10 * FLOOR(1 +(RAND() * 60)) + AUTO_INCREMENT
    FROM
        information_schema. TABLES
    WHERE
        table_name = "sdb_blog"
);
SET @FINALID = (
    IF (
        DATE_FORMAT(NOW(), '%H') > 6,
        @FINALID,
        NULL
    )
);
INSERT INTO sdb_blog (`id`)
VALUES
    (@FINALID);
ROLLBACK;
SET AUTOCOMMIT = 1;

END $$

DELIMITER ;

例子2. 时时刻刻每分钟增加0或1的例子:

DELIMITER $$

DROP EVENT IF EXISTS `事件名_randinc`;
CREATE EVENT `事件名_randinc`
ON SCHEDULE EVERY 1 MINUTE STARTS '2020-02-02 00:00:00' 
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT '每分钟随机增加自增id的自增值'
DO
BEGIN
SET AUTOCOMMIT = 0;
SET @FINALID = (
    SELECT FLOOR(RAND() * 2) + AUTO_INCREMENT
    FROM information_schema.TABLES
    WHERE table_schema = "数据库名" AND table_name = "表名全名"
);
INSERT INTO 表名全名(`id`) VALUES (@FINALID);
ROLLBACK;
SET AUTOCOMMIT = 1;

END $$
DELIMITER ;

版权声明,转载请附上原文链接及本声明: https://blog.yongit.com/note/143535.html