小編給大家分享一下MySQL如何實(shí)現(xiàn)event,相信大部分人都還不怎么了解,因此分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后大有收獲,下面讓我們一起去了解一下吧!
創(chuàng)新互聯(lián)公司電話聯(lián)系:18980820575,為您提供成都網(wǎng)站建設(shè)網(wǎng)頁設(shè)計(jì)及定制高端網(wǎng)站建設(shè)服務(wù),創(chuàng)新互聯(lián)公司網(wǎng)頁制作領(lǐng)域十年,包括成都石牌坊等多個(gè)行業(yè)擁有豐富的網(wǎng)站運(yùn)維經(jīng)驗(yàn),選擇創(chuàng)新互聯(lián)公司,為企業(yè)錦上添花。
1、開啟mysql event(事件)
mysql> show variables like '%event_sch%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
開啟event(事件)
vi /etc/my.cnf
[mysql]
event_scheduler=on
重啟數(shù)據(jù)庫
service mysql restart
mysql> show variables like '%event_sch%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)
2、創(chuàng)建event
DELIMITER $$
CREATE event event_p2
ON SCHEDULE
EVERY 5 MINUTE
STARTS NOW()
DO
BEGIN
CALL p2(160105);
END$$
DELIMITER ;
3、查看event
mysql> show events\G;
*************************** 1. row ***************************
Db: report
Name: event_p2
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2016-05-10 20:09:50
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
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}
其中,
event_name:定時(shí)器名,最大長(zhǎng)度64個(gè)字符,若未指定,則默認(rèn)為當(dāng)前的MySQL用戶名(不區(qū)分大小寫);
schedule:限定執(zhí)行時(shí)間;
ON COMPLETION [NOT] PRESERVE:表示是否需要循環(huán)復(fù)用這個(gè)Event;
sql_statement:要執(zhí)行的SQL語句(也可以使用存儲(chǔ)過程代替?zhèn)鹘y(tǒng)的SQL語句);
comment:對(duì)該時(shí)間調(diào)度器的一個(gè)注釋,最大長(zhǎng)度64個(gè)字符;
【關(guān)閉事件】
ALTER EVENT event_name ON COMPLETION PRESERVE DISABLE;
【開啟事件】
ALTER EVENT event_name ON COMPLETION PRESERVE ENABLE;
【刪除事件】
DROP EVENT [IF EXISTS] event_name
【注意】:要使用定時(shí)器,MySQL的常量GLOBAL event_scheduler必須為on或者是1.
【范例】
1. 每天凌晨1點(diǎn)開始執(zhí)行數(shù)據(jù)更新:
CREATE EVENT [IF NOT EXISTS] E_testEvent_1
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
call p2();
end ;
2. 每月第一天凌晨1點(diǎn)開始執(zhí)行數(shù)據(jù)更新(使用存儲(chǔ)過程):
CREATE EVENT E_testEvent_2
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 p2();
END
3. 每季度第一天凌晨1點(diǎn)開始執(zhí)行數(shù)據(jù)更新:
CREATE EVENT E_testEvent_3
ON SCHEDULE EVERY 1 QUARTER STARTS DATE_ADD(DATE_ADD(DATE( CONCAT(YEAR(CURDATE()),'-',ELT(QUARTER(CURDATE()),1,4,7,10),'-',1)),INTERVAL 1 QUARTER),INTERVAL 2 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
UPDATE _T_test SET col= 2 ;
以上是“mysql如何實(shí)現(xiàn)event”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對(duì)大家有所幫助,如果還想學(xué)習(xí)更多知識(shí),歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道!
分享名稱:mysql如何實(shí)現(xiàn)event
標(biāo)題路徑:http://bm7419.com/article0/gocsoo.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供網(wǎng)站維護(hù)、網(wǎng)站內(nèi)鏈、全網(wǎng)營(yíng)銷推廣、云服務(wù)器、外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站改版
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)