MYsql建存储过程、定时器、触发器

一、定时器

#查看当前是否已开启事件调度器
SHOW VARIABLES LIKE 'event_scheduler';

#要想保证能够执行event事件，就必须保证定时器是开启状态，默认为关闭状态
set global event_scheduler =1;
#或者set GLOBAL event_scheduler = ON;

开启事件test_event
alter event test_event on completion preserve enable;
关闭事件test_event
alter event test_event on completion preserve disable;

建立定时任务：
DROP EVENT IF EXISTS JOB_GXYHZT;  
CREATE EVENT JOB_GXYHZT  
 ON SCHEDULE EVERY 2 MINUTE  
DO    
     CALL  UpdateUserUtatus();  

二、建立存储过程

DELIMITER $$

USE `bypx`$$

DROP PROCEDURE IF EXISTS `ALLOT_MSG`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ALLOT_MSG`()
BEGIN  
    DECLARE user_uuid VARCHAR(36);
    DECLARE done INT;  
    DECLARE My_Cursor CURSOR FOR (SELECT UUID FROM sms_user t WHERE t.isactiv = '1' AND t.isadmin = '1' AND t.status = '1'); -- 定义游标并输入结果集  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 0;
    OPEN My_Cursor;
    myLoop: LOOP   
    FETCH My_Cursor INTO user_uuid;
    IF done=0 THEN
    LEAVE myLoop;
    END IF;  
UPDATE sms_sendmsg SET belong_service = user_uuid,isallot='1' WHERE UUID IN (SELECT UUID FROM(SELECT * FROM sms_sendmsg  WHERE isallot = '0' AND STATUS = '1' LIMIT 0,10) AS a);

COMMIT;

END LOOP myLoop;

CLOSE My_Cursor;

END$$

DELIMITER ;

三、触发器

DELIMITER $$
    CREATE TRIGGER creattime BEFORE INSERT ON sms_user  
    FOR EACH ROW SET NEW.lastvisittime = CURRENT_TIMESTAMP() ;
    $$
DELIMITER ;