`
ssydxa219
  • 浏览: 606055 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
文章分类
社区版块
存档分类
最新评论

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);
分享到:
评论

相关推荐

    mysql定时备份器java版

    本软件Mysql定时备份器,目前为beta v1.0版(测试版),基于JAVA编写,运行时需JVM1.5版本以上支持.可以帮助windows环境下的mysql用户实现自动备份功能.分完全备份和增量备份,可立即执行和定期执行.点击'完全备份'和'增量...

    mysql定时job

    mysql的定时job的一个简单应用,采用mysql存储过程的调用方式执行任务。该任务中涉及到使用游标来完成多个update执行过程。

    Linux下如何实现Mysql定时任务

    假设前提:每天晚上10点到早上5点,每10分钟定时执行存储过程。 实现方式: 第一种是利用Mysql本身实现,制定event定时任务,可以借助Navicat For Mysql或其他数据库开发工具去做; 第二种实现方式是利用linux的定时...

    MySQL定时执行脚本(计划任务)命令实例

    创建存储过程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 ...

    SQL Server 2005 定时执行SQL语句的方法

    不过要想更加直观的控制,直接写一个程序,定时执行你的存储过程。 1、设置“SQL Server 代理”(SQL Server Agent)服务随系统启动 –我的电脑–控制面板–管理工具–服务–右键SQLSERVERAGENT–属性–启动类型–选择...

    mysql七天定时下架任务

    这是我自己写的一个每天定时执行一次发布超过七天的岗位信息通过mysql数据库中的事件定时执行对应的存储过程,里面有详细的解释,无论你有没有接触过,都能看懂我写的意思!

    mysql 让一个存储过程定时作业的代码

    1、在mysql 中建立一个数据库 test1 语句:create database test1 2、创建表examinfo create table examinfo( id int ... 4 创建存储过程test CREATE PROCEDURE test () BEGIN update examinfo SET endtime = now() WH

    mysql中event事件使用详解.docx

    Event即事件,也是一种数据库对象,又叫时间触发器或者事件调度器,与triggers的事件触发不同,tiggers会因为update、delete、insert等...系统会触发相关的SQL语句或存储过程,相当于我们给存储过程创建了定时任务。

    MySQL存储过程创建实例,双循环结果集并定时执行

    使用navicat创建存储过程 BEGIN #Routine body goes here... DECLARE startTime DATETIME default date_sub(now(),interval 60 MINUTE);#起始时间 当前时间前一小时 DECLARE endTime DATETIME default NOW();...

    如何利用Crontab为Linux定时备份Mysql数据库

    利用系统Crontab来定时执行备份文件,按日期对备份结果进行保存,可以达到备份的目的。本文介绍了利用Crontab为Linux定时备份Mysql数据库的步骤。

    mssql和mysql打包存储

    本实例每逢周2,4,6定时备份windows 2003系统上的mysql和mssql数据库。并替换旧的备份。 0.根据数据库存放目录和数据备份目录设置好backupsql.bat文件 1.运行step1.bat创建备份数据库用户 2.运行step2.bat拷贝备份...

    mysql定时任务(event事件)实现详解

    事件取代了原先只能由操作系统的计划任务来执行的工作,而且MySQL的事件调度器可以精确到每秒钟执行一个任务,而操作系统的计划任务(如:Linux下的CRON或Windows下的任务计划)只能精确到每分钟执行一次。...

    MySQL数据库Event定时执行任务详解

    一、背景  由于项目的业务是不断往前跑的,所以难免数据库的表的量...#1、建立存储过程供事件调用 delimiter// drop procedure if exists middle_proce// create procedure middle_proce() begin DELETE FROM jg_bj_co

    聊聊高并发高可用那些事(Kafka、Redis、MySQL)

    - 存储过程、定时任务 - MySQL 视图 (VIEW) - Redis 和 MySQL 双写一致性 - 高并发高可用方案 - Join语句还能不能用? - 主从同步有延迟怎么办? - .frm .myi .myd .ibd 文件 - 为什么表数据删掉一半,表文件大小不变...

    逐步讲解MySQL中定时事件计划的创建

    一、使用过程 1.查看当前是否已开启事件计划(调度器)有3种方法: SHOW VARIABLES LIKE 'event_scheduler'; SELECT @@event_scheduler; SHOW PROCESSLIST;   2. 开启事件计划(调度器)开关有4种方法: SET ...

    自动备份MySQL数据库

    自动备份MySQL数据库是指通过设置定期执行的任务或脚本,自动将MySQL数据库的数据和结构进行备份,以保护数据的安全性和可...日志记录:自动备份过程应该有日志记录,以便检查备份的执行情况和出现的错误。日志记录可以

    SQLServer 2000定时执行SQL语句

    我不知道存储过程等能否实现,但是SQL Server 2000的作业调度确实有这样的功能,怎样使某些SQL语句定时执行呢,依次进行下面的操作: 企业管理器 –管理 –SQL Server代理 –右键作业 –新建作业 –“常规”...

    mysql数据库my.cnf配置文件

    # 0:如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘,提交事务的时候不做任何操作(执行是由mysql的master thread线程来执行的。 # 主线程中每秒会将重做日志缓冲写入磁盘的...

    MySQL Event Scheduler(事件调度器)

    一、概述 事件调度器是在 MySQL 5.1 中新增的另一个特色功能,可以作为定时任务调度器,取代部分原先只能用操作系统任务调度器才能完成的定时功&gt;能。例如,Linux 中的 crontabe 只能精确到每分钟执行一次,而 MySQL ...

    MySQL性能调优与架构设计 - 简朝阳.mobi

    4.1、 统计类去除实时查询,定时执行保存到表,读取数据 4.2、大文本数据,多媒体数据不存储 4.3、常用查询冗余字段,争取不用关联表能取到数据 4.4、先优化执行频率最高的,再优化频率低的 4.5、mysql用的是b+树,...

Global site tag (gtag.js) - Google Analytics