MYSQL定时执行存储过程

//**MYSQL定时执行存储过程
查看event是否开启: show variables like '%sche%';
将事件计划开启: set global event_scheduler=1;
关闭事件任务: alter event e_test ON COMPLETION PRESERVE DISABLE;
开户事件任务: alter event e_test ON COMPLETION PRESERVE ENABLE;

简单实例.
创建表 CREATE TABLE test(endtime DATETIME);

创建存储过程test
     CREATE PROCEDURE test ()
     BEGIN
          update examinfo SET endtime = now() WHERE id = 14;
     END;

     创建event e_test
     CREATE EVENT if not exists e_test
          on schedule every 30 second
          on completion preserve
     do call test();

     每隔30秒将执行存储过程test,将当前时间更新到examinfo表中id=14的记录的endtime字段中去.

//** MYSQL取一个月前一个月后的时间
 date_add() 增加
 date_sub()减少
 
 month 月份
 minute 分钟
 second 秒

 例如：select DATE_ADD(NOW(),INTERVAL 1 MONTH); //一个月后的时间

 

 

 、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、

  重新定义mysql命令行结束符为//，命令行创建存储过程需要。
delimiter //
MySQL5.1.x版本中引入了一项新特性EVENT，顾名思义就是事件、定时任务机制，在指定的时间单元内执行特定的任务，因此今后一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。
要查看当前是否已开启事件调度器，可执行如下SQL：
SHOW VARIABLES LIKE 'event_scheduler';
或
SELECT @@event_scheduler;
或
SHOW PROCESSLIST;
若显示：
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
则可执行
SET GLOBAL event_scheduler = 1;
或
SET GLOBAL event_scheduler = ON;
来开启，也可以直接在启动命令加上“–event_scheduler=1”，例如：
mysqld ... --event_scheduler=1
my.ini or my.cnf 中的
[mysqld]
添加 event_scheduler=ON
创建事件(CREATE EVENT)
先来看一下它的语法：
CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement;
schedule:
AT TIMESTAMP [+ INTERVAL INTERVAL]
| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]
INTERVAL:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
1)首先来看一个简单的例子来演示每秒插入一条记录到数据表
USE test;
CREATE TABLE aaa (timeline TIMESTAMP);
CREATE EVENT e_test_insert
ON SCHEDULE EVERY 1 SECOND
DO INSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP);
等待3秒钟后，再执行查询成功。
2) 5天后清空test表：
CREATE EVENT e_test
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
3) 2007年7月20日12点整清空test表：
CREATE EVENT e_test
ON SCHEDULE AT TIMESTAMP '2007-07-20 12:00:00'
DO TRUNCATE TABLE test.aaa;
4) 每天定时清空test表：
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
DO TRUNCATE TABLE test.aaa;
5) 5天后开启每天定时清空test表：
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
6) 每天定时清空test表，5天后停止执行：
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO TRUNCATE TABLE test.aaa;
7) 5天后开启每天定时清空test表，一个月后停止执行：
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
DO TRUNCATE TABLE test.aaa;
[ON COMPLETION [NOT] PRESERVE]可以设置这个事件是执行一次还是持久执行，默认为NOT PRESERVE。
8) 每天定时清空test表(只执行一次，任务完成后就终止该事件)：
CREATE EVENT e_test
ON SCHEDULE EVERY 1 DAY
ON COMPLETION NOT PRESERVE
DO TRUNCATE TABLE test.aaa;
[ENABLE | DISABLE]可是设置该事件创建后状态是否开启或关闭，默认为ENABLE。
[COMMENT ‘comment’]可以给该事件加上注释。
修改事件(ALTER EVENT)
ALTER EVENT event_name
[ON SCHEDULE schedule]
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT 'comment']
[ENABLE | DISABLE]
[DO sql_statement]
1) 临时关闭事件
ALTER EVENT e_test DISABLE;
2) 开启事件
ALTER EVENT e_test ENABLE;
3) 将每天清空test表改为5天清空一次：
ALTER EVENT e_test
ON SCHEDULE EVERY 5 DAY;
删除事件(DROP EVENT)
语法很简单，如下所示：
DROP EVENT [IF EXISTS] event_name
例如删除前面创建的e_test事件
DROP EVENT e_test;
当然前提是这个事件存在，否则会产生ERROR 1513 (HY000): Unknown event错误，因此最好加上IF EXISTS
DROP EVENT IF EXISTS e_test;

 

 。、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、。

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);