MySQL事件Event

MySQL事件Event_scheduler

1、SHOW VARIABLES LIKE 'event_scheduler';

2、事件的概念

事件=计划任务

自动化

每隔5秒钟，启动一个存储过程或者脚本程序

3、创建事件

（1）创建一个事件

SET GLOBAL event_scheduler=1;

USE db;
<pre name="code" class="sql">

CREATE EVENT myadd ON SCHEDULE EVERY 5 SECONDDO INSERT db.stu VALUES('user');

（2）一个事件内多个操作

<pre name="code" class="sql">DELIMITER //
CREATE EVENT myadd_event ON SCHEDULE EVERY 5 SECOND
DO
BEGIN
        DECLARE i INT DEFAULT 0;
        SELECT count(*) INTO i FROM stu;
        IF i>=15 THEN
                 call del3();
        END IF;
        INSERT stu VALUES(null,'user1');
        INSERT stu VALUES(null,'user2');    
        INSERT stu VALUES(null,'user3');
END //
DELIMITER ;
<span style="background-color: rgb(255, 255, 255);"></span>

游标的学习

DELIMITER //

CREATE PROCEDURE del3()

BEGIN

        DECLARE a char(16);

        DECLARE done INT DEFAULT 0;

        DECLARE cur CURSOR FOR SELECT id FROM stu LIMIT 3;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

        OPEN cur;

        read_loop: loop

                FETCH cur into a;

                IF done THEN

                                        LEAVE read_loop;

                END IF;

                DELETE FROM stu WHERE id=a;

         END LOOP;

         CLOSE cur;

END //

DELIMITER ;