MySQL事件的创建和执行

建立一个MySQL事件，首先我们需要建立一个或多个储存过程。示例如下

CREATE DEFINER=`vike0906`@`localhost` PROCEDURE `forTest`()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    SET @startTime = date_sub(curdate(),INTERVAL 0 DAY);
    SET @testSql=CONCAT("INSERT INTO hot_ali_bind 
    (uid, channel, ali_num, ali_name, updatetime)
    
    (SELECT a.uid uid, b.channel channel, a.u_high ali_num, a.u_name ali_name, ? updatetime
    FROM 
    hot_userinfo a LEFT JOIN hot_call_deduction_log b ON a.uid = b.userId 
    WHERE 
    a.id = 319)
    ");
    PREPARE stmt FROM @testSql;
    EXECUTE stmt USING @startTime;
    DEALLOCATE PREPARE stmt;
END

过程体：

BEGIN
    SET @startTime = date_sub(curdate(),INTERVAL 0 DAY);
    SET @testSql=CONCAT("INSERT INTO hot_ali_bind 
    (uid, channel, ali_num, ali_name, updatetime)
    
    (SELECT a.uid uid, b.channel channel, a.u_high ali_num, a.u_name ali_name, ? updatetime
    FROM 
    hot_userinfo a LEFT JOIN hot_call_deduction_log b ON a.uid = b.userId 
    WHERE 
    a.id = 319)
    ");
    PREPARE stmt FROM @testSql;
    EXECUTE stmt USING @startTime;
    DEALLOCATE PREPARE stmt;
END

紧接着我们来建立一个一分钟执行一次的事件：

CREATE DEFINER=`vike0906`@`localhost` EVENT `ceshi`
    ON SCHEDULE
        EVERY 1 MINUTE STARTS '2018-03-22 16:53:23'
    ON COMPLETION PRESERVE
    ENABLE
    COMMENT '我是备注'
    DO BEGIN

CALL forTest();

END

一个事件可以执行多个储存过程，只需要添加CALL xx();就行

在时间建立完成后发现事件并没有按我们设想的去执行，此时我们需要去查看数据库的事件是否开启（两条SQL作用一样）

show variables like 'event%';

show variables like 'event_scheduler';

结果如图：

Variable_name	Value
event_scheduler	OFF

 

 

此时我们需要开启事件(两条SQL作用一样)：

set global event_scheduler = NO;

set global event_scheduler = 1;

至此我们发现事件已经能够按我们设想的那样执行了。

另.

在my.cnf中添加event_scheduler=ON，可以使MySQL数据库在重启时事件自动开启，Windos下是my.ini,在MySQL的安装目录。

 

转载于:https://www.cnblogs.com/vike0906/p/8628622.html