mysql定义存储过程每天定时调用_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);