MySql事件

今天碰到了要求系统自动删除一个月之前的日志记录的需求，日志存在mysql数据库表中，之前的做法都是利用linux crontab定时任务去完成，今天突然发现了mysql的事件机制，便记录下来，分享给大家和自己。

Mysql事件学习

在系统管理或者数据库管理中，经常要周期性的执行某一个命令或者SQL语句。对于linux系统熟悉的人都知道linux的cron计划任务，能很方便地实现定期运行指定命令的功能。Mysql在5.1以后推出了事件调度器(Event Scheduler)，和linux的cron功能一样，能方便地实现 mysql数据库的计划任务，而且能精确到秒。使用起来非常简单和方便。

由于最近需要用到事件这个功能，因此学习了一下，感觉非常棒，总结一下，方便以后使用，也希望能对其他的初学者有帮助。

一、   如果开启事件

在使用事件这个功能，首先要保证你的mysql的版本是5.1以上，然后还要查看你的mysql服务器上的事件是否开启。

查看事件是否开启，使用如下命令查看：

SHOW VARIABLES LIKE 'event_scheduler';

SELECT @@event_scheduler;

SHOW PROCESSLIST;

如果看到event_scheduler为on或者PROCESSLIST中显示有event_scheduler的信息说明就已经开启了事件。如果显示为off或者在PROCESSLIST中查看不到event_scheduler的信息，那么就说明事件没有开启，我们需要开启它。

开启mysql的事件，通过如下三种方式开启：

Ø  通过动态参数修改

SET GLOBAL event_scheduler = ON;

更改完这个参数就立刻生效了

注意：还是要在my.cnf中添加event_scheduler=ON。因为如果没有添加的话，mysql重启事件又会回到原来的状态了。

Ø  更改配置文件然后重启

在my.cnf中的[mysqld]部分添加如下内容，然后重启mysql。

event_scheduler=ON

Ø  通过制定事件参数启动

mysqld ... --event_scheduler=ON

 

二、   Mysql事件的语法简介

1.       创建事件的语法

CREATE

    [DEFINER = { user | CURRENT_USER }]

    EVENT

    [IF NOT EXISTS]

    event_name

    ON SCHEDULE schedule

    [ON COMPLETION [NOT] PRESERVE]

    [ENABLE | DISABLE | DISABLE ON SLAVE]

    [COMMENT 'comment']

    DO event_body;

 

schedule:

    AT timestamp [+ INTERVAL interval] ...

     | EVERY interval

    [STARTS timestamp [+ INTERVAL interval]...]

    [ENDS timestamp [+ INTERVAL interval] ...]

interval:

  quantity {YEAR | QUARTER | MONTH | DAY | HOUR| MINUTE |

              WEEK | SECOND | YEAR_MONTH |DAY_HOUR |

DAY_MINUTE |DAY_SECOND| HOUR_MINUTE |

HOUR_SECOND| MINUTE_SECOND}

参数详细说明：

DEFINER: 定义事件执行的时候检查权限的用户。

ON SCHEDULE schedule: 定义执行的时间和时间间隔。

ON COMPLETION [NOT] PRESERVE: 定义事件是一次执行还是永久执行，默认为一次执行，即NOT PRESERVE。

ENABLE | DISABLE | DISABLE ON SLAVE: 定义事件创建以后是开启还是关闭，以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话，会自动加上DISABLE ON SLAVE。

COMMENT 'comment': 定义事件的注释。

 

2.       更改事件的语法

ALTER

    [DEFINER = { user | CURRENT_USER }]

    EVENT event_name

    [ON SCHEDULE schedule]

    [ON COMPLETION [NOT] PRESERVE]

    [RENAME TO new_event_name]

    [ENABLE | DISABLE | DISABLE ON SLAVE]

    [COMMENT 'comment']

    [DO event_body]

3.       删除事件的语法

DROP EVENT [IF EXISTS] event_name

三、   Mysql事件实战

1.        测试环境

创建一个用于测试的test表：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t1` datetime DEFAULT NULL,
  `id2` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8

2.        实战1

Ø  创建一个每隔3秒往test表中插入一条数据的事件，代码如下（自我测试了下貌似有错，不能执行，自己修改下吧，可以看##########下面的内容）：

CREATE EVENT IF NOT EXISTS test ON SCHEDULE EVERY 3 SECOND

ON COMPLETION PRESERVE

DO INSERT INTO test(id,t1)VALUES('',NOW());

Ø  创建一个10分钟后清空test表数据的事件

CREATE EVENT IF NOT EXISTS test

ON SCHEDULE

AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE

DO TRUNCATE TABLE test.aaa;

Ø  创建一个在2012-08-2300:00:00时刻清空test表数据的事件，代码如下：

CREATE EVENT IF NOT EXISTS test

ON SCHEDULE

AT TIMESTAMP '2012-08-23 00:00:00'

DO TRUNCATE TABLE test;

Ø  创建一个从2012年8月22日21点45分开始到10分钟后结束，运行每隔3秒往test表中插入一条数据的事件，代码如下：

CREATE EVENT IF NOT EXISTS test ON SCHEDULE EVERY 3 SECOND

STARTS '2012-08-22 21:49:00' 

ENDS '2012-08-22 21:49:00'+ INTERVAL 10 MINUTE

ON COMPLETION PRESERVE

DO INSERT INTO test(id,t1) VALUES('',NOW());

 

3.        实战2

通常的应用场景是通过事件来定期的调用存储过程，下面是一个简单的示例：

创建一个让test表的id2字段每行加基数2的存储过程，存储过程代码如下：

DROP PROCEDURE IF EXISTS test_add;

DELIMITER //

CREATE PROCEDURE test_add()

BEGIN

DECLARE 1_id INT DEFAULT 1;

DECLARE 1_id2 INT DEFAULT 0;

DECLARE error_status INT DEFAULT 0;

DECLARE datas CURSOR  FOR SELECT id FROM test;

DECLARE CONTINUE HANDLER FOR NOTFOUND SET error_status=1;

OPEN datas;

FETCH datas INTO 1_id;

REPEAT

SET 1_id2=1_id2+2;

UPDATE test SET id2=1_id2 WHEREid=1_id;

FETCH datas INTO 1_id;

UNTIL error_status

END REPEAT;

CLOSE datas;

END

//

事件设置2012-08-2200:00:00时刻开始运行，每隔1调用一次存储过程，40天后结束，代码如下：

CREATE EVENT test ON SCHEDULE EVERY 1DAY

STARTS '2012-08-22 00:00:00'

ENDS '2012-08-22 00:00:00'+INTERVAL40 DAY

ON COMPLETION PRESERVE DO

CALL test_add();

 

四、   参考链接

http://dev.mysql.com/doc/refman/5.1/en/events-configuration.html

http://dev.mysql.com/doc/refman/5.1/en/create-event.html

http://blog.163.com/duanpeng3@126/blog/static/8854373520105182123112/

#################################

Mysql定时任务(定时执行操作)

Posted on 2009-12-18 09:42 烟头上的探戈 阅读(1298) 评论(0) 编辑 收藏

Mysql定时任务(定时执行操作)
Mysql属于中小型数据库系统，它的事件调度器Event Scheduler是在mysql 5.1才开始引入
事件调度器是在 MySQL 5.1 中新增的另一个特色功能，可以作为定时任务调度器，
取代部分原先只能用操作系统任务调度器才能完成的定时功能。事件调度器是定时触发执行的，
在这个角度上也可以称作是"临时的触发器"。触发器只是针对某个表产生的事件执行一些语句，
而事件调度器则是在某一个(间隔)时间执行一些语句。事件是由一个特定的线程来管理的，

也就是所谓的"事件调度器"。

代码

/* 开启event_scheduler，也可以设置为on */
set  global event_scheduler  = 1 ;
/* 创建简单的任务，每分钟执行一次，从一个表取数据插入到另一个表，也可以定时取，可以参看下面文档，此处为简单例子 */
CREATE  EVENT MyEvent
ON  SCHEDULE EVERY  1  MINUTE 
DO
INSERT   INTO  list_test (字段1,字段1,字段1,字段1,字段1)  select  字段1,字段1,字段1,字段1,字段1  from  表2  where  id   =   510 ;
/*每天固定时间执行*/
CREATE EVENT EVENT_ADD_FOR20_ENOUGH
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP '2009-12-18 02:58:00'/×MYSQL注意时区设置，默认非中国时区×/
DO
SQL语句

/Files/pochonlee/MySQL-Events-CN.rar

再加两段吧

代码

DELIMITER $$

DROP   PROCEDURE   IF   EXISTS  `mystock`.`TEST_KKK`$$

CREATE   PROCEDURE  `mystock`.`TEST_KKK`()
     /* LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string' */
     BEGIN
     DECLARE  NUM  INTEGER   DEFAULT   0 ;

     DECLARE  $A  INT ;

     SELECT  ( 20 - COUNT (STOCK_CODE))  as   number   into  NUM  FROM  get_stock_list  WHERE  FLAG  =   0   AND  STIME  =  curdate();

     IF  NUM > 0   THEN

     PREPARE  STMP  FROM   ' INSERT INTO get_stock_list (stock_code,stock_name,close,raises,stime) select stock_code,stock_name,close,result1,selectd from choice_stock  where stock_code  not in (select stock_code from get_stock_list where stime = curdate() and flag = 0) and selectd = curdate() and selectd = curdate() order by id limit ? '  ;

     SET   @A   =  NUM;

     EXECUTE  STMP USING  @A ;

     END   IF ;
     END $$

DELIMITER ;

 

 

代码

DELIMITER $$

DROP   FUNCTION   IF   EXISTS  `mystock`.`FUNCTION_ADD_STOCK_FOR20`$$

CREATE  DEFINER = `root`@`localhost`  FUNCTION  `FUNCTION_ADD_STOCK_FOR20`()  RETURNS   int ( 11 )
BEGIN
     DECLARE  NUM,ANOTHER  INTEGER ;
     SELECT   COUNT (STOCK_CODE)  as   number   into  NUM  FROM  get_stock_list  WHERE  FLAG  =   0   AND  STIME  =  curdate();
     if (NUM < 20 )
     THEN  
         SET  ANOTHER  =   20   -  NUM;
         INSERT   INTO  get_stock_list (stock_code,stock_name, close ,raises,stime)  select  stock_code,stock_name, close ,result1,selectd  from  choice_stock  where  stock_code   not   in  ( select  stock_code  from  get_stock_list  where  stime  =  curdate()  and  flag  =   0 )  order   by  id LIMIT  10 ;
     END   IF ;
     RETURN  NUM;
     END $$

DELIMITER ;

*****************************************************************************************************************************

一、基本概念
mysql5.1版本开始引进event概念。event既“时间触发器”，与triggers的事件触发不同，event类似与linux crontab计划任务，用于时间触发。通过单独或调用存储过程使用，在某一特定的时间点，触发相关的SQL语句或存储过程。

二、适用范围
对于每隔一段时间就有固定需求的操作，如创建表，删除数据等操作，可以使用event来处理。

例如：使用event在每月的1日凌晨1点自动创建下个月需要使用的三张表。

三、使用权限
单独使用event调用SQL语句时，查看和创建需要用户具有event权限，调用该SQL语句时，需要用户具有执行该SQL的权限。Event权限的设置保存在mysql.user表和mysql.db表的Event_priv字段中。

当event和procedure配合使用的时候，查看和创建存储过程需要用户具有create routine权限，调用存储过程执行时需要使用excute权限，存储过程调用具体的SQL语句时，需要用户具有执行该SQL的权限。
查看EVENT命令有如下几种：
 
（1）查询mysql.event表；

（2）通过SHOW EVENTS命令；

（3）通过SHOW FULL EVENTS命令；

（4）通过查询information_schema.events表

（5）SHOW CREATE EVENT。
 
总之，event的使用频率较低建议使用root用户进行创建和维护。

四、基本语法

4.1 开启定时器
要使event起作用，MySQL的常量GLOBAL event_scheduler必须为on或者是1。
-- 查看是否开启定时器

复制代码 代码如下:

SHOW VARIABLES LIKE 'event_scheduler';

-- 开启定时器 0：off 1：on

复制代码 代码如下:

SET GLOBAL event_scheduler = 1;

当你设定事件计划为0 或OFF，即关闭事件计划进程的时候，不会有新的事件执行，但现有的正在运行的事件会执行到完毕

对于我们线上环境来说，使用event时，注意在主库上开启定时器，从库上关闭定时器，event触发所有操作均会记录binlog进行主从同步，从库上开启定时器很可能造成卡库。切换主库后之后记得将新主库上的定时器打开。
请特别注意！

4.2 创建事件
CREATE EVENT 的语法如下：
CREATE EVENT
[IF NOT EXISTS] ---------------------------------------------*标注1
event_name -----------------------------------------------------*标注2

ON SCHEDULE schedule ------------------------------------*标注3
[ON COMPLETION [NOT] PRESERVE] -----------------*标注4
[ENABLE | DISABLE] ----------------------------------------*标注5
[COMMENT 'comment'] --------------------------------------*标注6
DO sql_statement -----------------------------------------------*标注7

说明：
 
标注1：[IF NOT EXISTS]
 
使用IF NOT EXISTS，只有在同名event不存在时才创建，否则忽略。建议不使用以保证event创建成功。

标注2：event_name
 
名称最大长度可以是64个字节。名字必须是当前Dateabase中唯一的，同一个数据库不能有同名的event。

使用event常见的工作是创建表、插入数据、删除数据、清空表、删除表。

为了避免命名规范带来的不便，最好让事件名称具有描述整个事件的能力。建议命名规则如下为：动作名称_（INTO/FROM_）表名_TIME，例如：
1.     每月创建（清空/删除）fans表： 
create(truncate/drop)_table_fans_month；
2.     每天从fans表插入（删除）数据：
insert(delete)_into(from)_fans_day；
标注3：ON SCHEDULE
 
ON SCHEDULE 计划任务，有两种设定计划任务的方式：
 
1. AT 时间戳，用来完成单次的计划任务。

2. EVERY 时间（单位）的数量时间单位[STARTS 时间戳] [ENDS时间戳]，用来完成重复的计划任务。

在两种计划任务中，时间戳可以是任意的TIMESTAMP 和DATETIME 数据类型，时间戳需要大于当前时间。

在重复的计划任务中，时间（单位）的数量可以是任意非空（Not Null）的整数式，时间单位是关键词：YEAR，MONTH，DAY，HOUR，MINUTE 或者SECOND。

提示: 其他的时间单位也是合法的如：QUARTER, WEEK, YEAR_MONTH,DAY_HOUR,DAY_MINUTE,DAY_SECOND,HOUR_MINUTE,HOUR_SECOND, MINUTE_SECOND，不建议使用这些不标准的时间单位。

标注4： [ON COMPLETION [NOT] PRESERVE]
 
ON COMPLETION参数表示"当这个事件不会再发生的时候"，即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS阶段。而PRESERVE的作用是使事件在执行完毕后不会被Drop掉，建议使用该参数，以便于查看EVENT具体信息。

标注5：[ENABLE | DISABLE]
参数Enable和Disable表示设定事件的状态。Enable表示系统将执行这个事件。Disable表示系统不执行该事件。

可以用如下命令关闭或开启事件：

ALTER EVENT event_name  ENABLE/DISABLE
标注6：[COMMENT 'comment']
 
注释会出现在元数据中，它存储在information_schema表的COMMENT列，最大长度为64个字节。'comment'表示将注释内容放在单引号之间，建议使用注释以表达更全面的信息。

标注 7: DO sql_statement
 
DO sql_statement字段表示该event需要执行的SQL语句或存储过程。这里的SQL语句可以是复合语句，例如：
BEGIN
CREATE TABLE test1;//创建表（需要测试一下）
DROP TABLE test2;//删除表
CALL proc_test1();//调用存储过程
END

使用BEGIN和END标识符将复合SQL语句按照执行顺序放在之间。当然SQL语句是有限制的，对它的限制跟函数Function和触发器Trigger 中对SQL语句的限制是一样的，如果你在函数Function 和触发器Trigger 中不能使用某些SQL，同样的在EVENT中也不能使用。明确的来说有下面几个：

LOCK TABLES
UNLOCK TABLES
CREATE EVENT
ALTER EVENT
LOAD DATA

4.3  执行逻辑
For (已建立事件each event that has been created)
If (事件的状态非DISABLE)
And (当前时间在ENDS时间之前)
And (当前时间在STARTS时间之后)
And (在上次执行后经过的时间)
And (没有被执行)
Then:
建立一个新的线程
传递事件的SQL语句给新的线程
(该线程在执行完毕后会自动关闭)

4.4 修改事件
使用ALTER EVENT 来修改事件，具体的ALTER语法如下，与创建事件的语法类似：
ALTER EVENT
event_name

ON SCHEDULE schedule
[RENAME TO new_event_name]
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement

 
4.5 删除事件
EVENT使用DROP EVENT语句来删除已经创建的事件，语法如下：

DROP EVENT
[IF EXISTS]
event_name

 
但当一个事件正在运行中时，删除该事件不会导致事件停止，事件会执行到完毕为止。使用DROP USER和DROP DATABASE 语句同时会将包含其中的事件删除。

五、常用实例
每隔一秒自动调用e_test()存储过程

复制代码 代码如下:

CREATE EVENT IF NOT EXISTS e_test
ON SCHEDULE EVERY 1 SECOND
ON COMPLETION PRESERVE
DO CALL e_test();

每个月的一号凌晨1 点执行STAT()存储过程：

复制代码 代码如下:

CREATE  EVENT  NOT EXISTS  STAT
ON  SCHEDULE  EVERY  1  MONTH  STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)
ON  COMPLETION  PRESERVE  ENABLE
DO
BEGIN
CALL STAT();
END