學(xué)習(xí)mysql事件如何調(diào)用存儲過程的方法

第一次寫事件調(diào)用存儲過程,在網(wǎng)上找了一些資料,特此做下總結(jié),鞏固一下:

事件調(diào)用存儲過程主要有三種:

(1)創(chuàng)建事件馬上執(zhí)行,調(diào)用存儲過程

?CREATE EVENT if not exists Event_Stat_Daily
????????? on schedule EVERY 1 DAY
????????? on completion preserve
???? do call cp_Stat_VideoData();

(2)每天定時執(zhí)行事件,調(diào)用存儲過程

CREATE EVENT Event_Stat_Daily
ON SCHEDULE EVERY 1 DAY STARTS 2017-03-01 02:00:00
ON COMPLETION PRESERVE
ENABLE
DO call cp_Stat_VideoData();

(3)沒有調(diào)用方法或者存儲過程,直接在事件里面邏輯操作

?

DELIMITER | CREATE EVENT e5 ON SCHEDULE EVERY 1 DAY STARTS? ‘2017-03-01 02:00:00’ ON COMPLETION PRESERVE DO BEGIN declare yestday date; set yestday=date(date_add(NOW(), interval -1 day)); if exists(select Id from Stat_VideoHits where AddDate = yestday) THEN delete from Stat_VideoHits where AddDate=yestday; end if;

insert into Stat_VideoHits(Id,VideoId,Times,AddDate) select uuid(), VideoId,COUNT(1),AddDate from Coll_VideoHits where AddDate = yestday group by VideoId;

DELETE from Sum_VideoHits;

insert into Sum_VideoHits(Id,VideoId,Times,UpdateDate) select uuid(),VideoId,sum(Times),now() from Stat_VideoHits group by VideoId;

END | DELIMITER ;

?

本文主要出自:

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊15 分享