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

標籤：into   stat   語句   狀態   建立   and   while   改變   center   

        在我們操作資料庫過程中，通常會遇到一些某個時間點操作資料庫的問題，例如：
        (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 testeventON SCHEDULE EVERY 1 DAY STARTS ‘2017-03-09 23:50:00‘  DOdelete 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     DOINSERT 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     DOupdate 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 eON SCHEDULE    EVERY 5 SECONDDOBEGIN    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/ )

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