Learn how mysql events call stored procedure methods

It is my first time to write an event call stored procedure. I found some information on the Internet. I would like to summarize and consolidate it:

There are three main types of event call stored procedures:

(1) The created event will be executed immediately, calling the stored procedure

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

(2) Execute events regularly every day and call the stored procedure

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) No method or stored procedure is called, the logic is directly in the event Operation

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;

This article Mainly from:

The above is the detailed content of Learn how mysql events call stored procedure methods. For more information, please follow other related articles on the PHP Chinese website!