Mysql事件學習

標籤：

轉：http://blog.chinaunix.net/uid-20639775-id-3323098.html

 

在系統管理或者資料庫管理中，經常要周期性的執行某一個命令或者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‘: 定義事件的注釋。

 

1. 變更事件的文法

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]

1. 刪除事件的文法

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

1. 實戰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-23 00: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());

 

1. 實戰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 NOT FOUND SET error_status=1;

OPEN datas;

FETCH datas INTO 1_id;

REPEAT

SET  1_id2=1_id2+2;

UPDATE test SET id2=1_id2 WHERE id=1_id;

FETCH datas INTO 1_id;

UNTIL  error_status

END REPEAT;

CLOSE  datas;

END

//

事件設定2012-08-22 00:00:00時刻開始運行，每隔1調用一次預存程序，40天后結束，代碼如下：

CREATE EVENT test ON SCHEDULE EVERY 1 DAY

STARTS ‘2012-08-22 00:00:00‘

ENDS ‘2012-08-22 00:00:00‘+INTERVAL 40 DAY

ON COMPLETION PRESERVE DO

CALL test_add();

Mysql事件學習