Mysql定时任务&存储过程

openkk 12年前
1、定时任务

1.1 简述

      Mysql 属于中小型  数据库 系统，它的事件调度器 Event Scheduler 是在  mysql 5.1 才开始引入事件调度器是在  MySQL 5.1  中新增的另一个特色功能，可以作为定时任务调度器，取代部分原先只能用操作系统任务调度器（如linux的crontab）才能完成的定时功能。事件调度器是定时触发执行的，在这个角度上也可以称作是  " 临时的触发器  " 。触发器只是针对某个表产生的事件执行一些语句，而事件调度器则是在某一个  ( 间隔  ) 时间执行一些语句。事件是由一个特定的线程来管理的，也就是所谓的  " 事件调度器  " 。 
1.2 查看开启调度器
  •        查看event是否开启 : SHOW VARIABLES LIKE '%event_sche%';
  •      将事件计划开启 : SET GLOBAL event_scheduler = 1; 
  •    关闭事件任务 : ALTER EVENT e_test ON COMPLETION PRESERVE DISABLE; 
  •      开启事件任务 : ALTER EVENT e_test ON COMPLETION PRESERVE ENABLE; 
  •      查看事件任务 : SHOW EVENTS ;
1.3 创建简单Demo  

 DELIMITER $$
      /*每天固定时间执行*/ 
 ALTER  EVENT `even_name` ON SCHEDULE EVERY 1 MINUTE STARTS '2012-01-13 00:00:00' [ON COMPLETION PRESERVE   ENABLE]
      /*非固定时间*/
/* ALTER  EVENT `even_name` ON SCHEDULE EVERY 1 MINUTE  [ON COMPLETION PRESERVE   ENABLE]*/
 DO BEGIN
    CALL TestPro();
  END$$

 DELIMITER ;

PS：MYSQL注意时区设置，默认非中国时区
     查看时区
     SHOW VARIABLES LIKE '%time_zone%';
     比如北京时间（ GMT+0800）
    set time_zone = ‘+8:00′; 
    system的话则跟操作系统同步

2、存储过程

2.1 简单Demo：

DELIMITER $$

DROP PROCEDURE IF EXISTS `TestPro`$$

CREATE [DEFINER=`root`@`localhost`] PROCEDURE `TestPro`()
BEGIN
       INSERT INTO SysRight(SysRightCode) VALUES(01000);
END$$
DELIMITER ;

2.2 U6_Demo

DROP PROCEDURE IF EXISTS  `ChannelStopHourLimited_3Minute` ;

DELIMITER $$

CREATE  PROCEDURE  `ChannelStopHourLimited_3Minute`()
BEGIN
/*
@author :
@cdate: 
功能：XXXXXXXXX
步骤：
         
相关表

         
执行频率：每三分钟一次， 执行时间：0:00 至23:59
*/     
         -- 遇到SQL异常后执行回滚
         DECLARE prost datetime;
         -- DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
         -- 将存储过程信息插入到 EventExecuteLog表
         SET prost = NOW();
         INSERT INTO EventExecuteLog VALUES ('ChannelStopHourLimited_3Minute',prost,'0000-00-00 00:00:00','fail');
         -- 开启事务
         -- START TRANSACTION;
         
         SET @ChannelIDs = '';
         -- 1 获取所有超额的通道
         SELECT @ChannelIDs := CONCAT(@ChannelIDs, b.ChannelID, ',') FROM Channel c INNER JOIN ChannelCycleBilling b
         ON c.ChannelSno=b.ChannelID WHERE (c.Status = 0) AND (c.ChannelDayMaxFee < 10000000) AND (b.ThisHourFee > c.ChannelDayMaxFee/24);
         
         -- 2 下调该通道权重（把当前值大于 0的改成负值）
         IF (@ChannelIDs <> '') THEN
                   SET @ChannelIDs = LEFT(@ChannelIDs, LENGTH(@ChannelIDs) - 1);
                   SET @mySql = CONCAT('UPDATE ChannelAreaWeight SET Weight = -Weight WHERE (ChannelID in (', @ChannelIDs, ') AND Weight > 0)');
                   PREPARE pstmt FROM @mySql ; -- 配置执行语句
                   EXECUTE pstmt;
                   DEALLOCATE PREPARE pstmt; -- 解除分配
         END IF;
         -- 设置此存储过程运行成功信息插入到 EventExecuteLog表
        UPDATE EventExecuteLog SET executetime=NOW(),Description='OK' WHERE StartTime=prost AND ProcedureName='ChannelStopHourLimited_3Minute';
        -- 提交事务
        -- COMMIT;
END $$
DELIMITER ;


PS：*  declare定义变量必须写在前面
           *  “--” 注释时，必须要带空格，也就是“ -- ”
           *   游标定义可以放在前面，即使有些临时表还没有生成。

2.3 游标

DECLARE CursorName CURSOR FOR SELECT field1, field2, .... FROM TableName; 

  -- 设置游标读取完毕后的标识
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET iStop = 1;

-- 打开游标
OPEN CursorName;
-- 读取记录
FETCH CursorName INTO ifield1, ifield2, ....;
WHILE  ( iStop <> 1) DO
                  INSERT INTO Table VALUES(ifield1, ifield2, ...);
                   -- 读取下一条数据
                 FETCH CursorName INTO ifield1, ifield2, ...;
 END WHILE;
 -- 关闭游标
CLOSE CursorName;

PS： 游标必须和定义变量一样，在存储过程开头定义

2.4 创建临时表

DROP  TEMPORARY TABLE IF EXISTS  `TableName`;
CREATE TEMPORARY TABLE TableName(ID INT, MtCnt INT);