mysql的event(事件)用法详解_mysql的event(事件)用法详解

爱被打了一巴掌 2022-10-25 05:00 220阅读 0赞

1 MySQL从5.1开始支持event功能，类似oracle的job功能。有了这个功能之后我们就可以让MySQL自动的执行数据汇总等功能，不用像以前需要操作的支持了。如linux crontab功能 。 -- 查看是否开启定时器 SHOW VARIABLES LIKE '%sche%'; +-----------------+-------+2

3 MySQL从5.1开始支持event功能，类似oracle的job功能。有了这个功能之后我们就可以让MySQL自动的执行数据汇总等功能，不用像以前需要操作的支持了。如linux crontab功能 。4

5 -- 查看是否开启定时器6 SHOW VARIABLES LIKE '%sche%';7

8 +-----------------+-------+9 | Variable\_name | Value |10 +-----------------+-------+11 | event\_scheduler | ON |12 +-----------------+-------+13 1 row in set14

18 -- 开启定时器 0：off 1：on19 SET GLOBAL event\_scheduler = 1;20

21 -- 创建事件22 --每隔一秒自动调用e\_test()存储过程23 CREATE EVENT IF NOT EXISTS event\_test24 ON SCHEDULE EVERY 1 SECOND25 ON COMPLETION PRESERVE26 DO CALL e\_test();27

28 -- 开启事件29 ALTER EVENT event\_test ON30 COMPLETION PRESERVE ENABLE;31

32 -- 关闭事件33 ALTER EVENT event\_test ON34 COMPLETION PRESERVE DISABLE;35

36 从现在开始每隔九天定时执行37 CREATE EVENT EVENT138

39 ON SCHEDULE EVERY 9 DAY STARTS NOW()40 ON COMPLETION PRESERVE ENABLE41 DO42 BEGIN43 CALL TOTAL();44 END45

50 每个月的一号凌晨1 点执行51 CREATE EVENT EVENT252

53 ON SCHEDULE EVERY 1 MONTH STARTS DATE\_ADD(DATE\_ADD(DATE\_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)54 ON COMPLETION PRESERVE ENABLE55 DO56 BEGIN57 CALL STAT();58 END59

62 每个季度一号的凌晨2点执行63

64 CREATE EVENT TOTAL\_SEASON\_EVENT65

66 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)67 ON COMPLETION PRESERVE ENABLE68 DO69 BEGIN70 CALL SEASON\_STAT();71 END72

76 每年1月1号凌晨四点执行77 CREATE EVENT TOTAL\_YEAR\_EVENT78

79 ON SCHEDULE EVERY 1 YEAR STARTS DATE\_ADD(DATE(CONCAT(YEAR(CURDATE()) + 1,'-',1,'-',1)),INTERVAL 4 HOUR)80 ON COMPLETION PRESERVE ENABLE81 DO82 BEGIN83 CALL YEAR\_STAT();84 END