[資料庫] Navicat for MySQL事件Event實現資料每日定期操作

Eastmount發表於2017-03-10
資料庫MySql事件

        在我們運算元據庫過程中，通常會遇到一些某個時間點運算元據庫的問題，例如：
        (1).每天凌晨12點對資料庫進行定時備份，結算和彙總；
        (2).每天凌晨2點刪除資料庫前三天的資料；
        (3).插入某個資料超過一定時間改變某個值的狀態，比如預警系統。
        這裡就需要通過Event事件進行簡單操作，下面將詳細處理。你可能會想到通過觸發器實現，但是如果是同一張表Insert插入資料後，但是觸發器再進行Update更新操作是不行的，所以需要嘗試通過Event事件解決。
        在前面講過一篇文章：[資料庫] Navicat for MySQL觸發器更新和插入操作
        希望這篇文章對你有所幫助，如果文章中出現錯誤或不足之處，還請海涵~


一. Event實現每日某個時刻刪除事件

        首先通過Navicat for MySQL檢視Event事件是否開啟，具體程式碼：

show variables like '%sche%';
        通常事件是開啟的，如下圖所示：


        如果沒有開啟，需要資料庫超級許可權設定。

set global event_scheduler =1;
        其中event_scheduler為ON表示開啟，OFF表示關閉，如下：
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
        下面開始寫事件進行操作。
        假設現在存在一張表loginuser，包括使用者名稱、密碼、許可權三個欄位。如下圖：



        當前時間點為2017年3月9日 23:48，我們設定23:50執行刪除eastmount資料操作。其中SQL語句的Event程式碼如下：
CREATE EVENT testevent
	ON SCHEDULE EVERY 1 DAY STARTS '2017-03-09 23:50:00'
  DO
		delete from loginuser where Username='eastmount';
        建立事件可以檢視到的，如下圖所示：



        然後過了23:50執行查詢語句就會發現eastmount使用者已經被刪除。


        點開事件如下圖所示，但我更推薦大家寫SQL語句。



        通過上面這段SQL語句，我們知道了最簡單的Event事件，下面進一步加深介紹。備份資料加個備份SQL語句執行即可。
        參考官網：https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html


二. Event實現每隔某段時間定時插入事件

        在使用事件Event中，需要開啟和關閉事件，使用的程式碼如下：
        關閉事件：ALTER EVENT testevent DISABLE;
        開啟事件：ALTER EVENT testevent ENABLE;
        檢視事件是否開啟的程式碼如下：

SELECT * FROM mysql.event;
        輸出如下圖所示：


        下面定義每隔10秒插入一行資料的事件，比如實時接收交通資料資訊等實時處理，通常會使用該方法進行插入操作。建立一個表logininfo，記錄登入資訊，欄位包括：ID序號（主鍵、遞增INT型）、NAME使用者名稱、STARTTIME（登入時間）、ENDTIME（登出時間）、STATE（狀態 0-離線 1-線上）。


        SQL語句建立EVENT事件如下：
CREATE EVENT insertevent
    ON SCHEDULE EVERY 10 SECOND 
    DO
				INSERT INTO logininfo(NAME,STARTTIME,STATE) values('test01',now(),'1');
        執行結果如下圖所示：


        從圖中可以看到，每隔10秒就插入一組資料，該事件正確執行。
        注意：在真實的開發過程中，會遇到mysql服務重啟或斷電等情況，此時則會出現時間排程器被關閉的情況，所有事件都不起作用，解決方法，需要在mysql.ini檔案中加入 "event_scheduler = ON;" 語句。



三. Event實現超過某個時間更新狀態

        假設現在有一個報警系統，當使用者進入房間開始計時，當使用者在放在中待了2小時就開始報警，設定狀態。資料如下：


        SQL語句查詢返回結果如下：
select current_timestamp, starttime, timestampdiff(SECOND,starttime,current_timestamp) as T from logininfo;
        輸出結果如下圖所示，每隔10秒會插入一個資料，所以不斷更新。

        現在的更新UPDATE的語句如下所示：

CREATE EVENT updateevent
    ON SCHEDULE EVERY 10 SECOND 
    DO
				update logininfo set STATE=0 WHERE 
					timestampdiff(SECOND,starttime,current_timestamp)>300 and STATE=1;
        然後查詢實時更新狀態的SQL語句如下：
select ID, STATE, current_timestamp, starttime, 
				timestampdiff(SECOND,starttime,current_timestamp) as T 
from logininfo;
        輸出如下圖所示，發現302可能是查過300秒，可能實時更新的延誤，但不影響效果的。



        講到這裡，一個簡單預警系統的資料庫就設定完成，總共我們設定了3個事件，如下圖所示：


        補充一段程式碼：

delimiter //
CREATE EVENT e
ON SCHEDULE
    EVERY 5 SECOND
DO
BEGIN
    DECLARE v INTEGER;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
    SET v = 0;
    WHILE v < 5 DO
        INSERT INTO t1 VALUES (0);    
        UPDATE t2 SET s1 = s1 + 1;
        SET v = v + 1;
    END WHILE;
END //
delimiter ;

        最後希望文字對您有所幫助，基礎性文章，如果存在錯誤或不足之處，還請海涵~
        最近自己和她太操勞，自己需要多付出點，多學點，多為她做點，forever。
       (By:Eastmount 2017-03-10 中午12點   http://blog.csdn.net//eastmount/ )


相關文章