MySQL事件及存储过程例举

1.查看是否开启event事件功能

SHOW VARIABLES LIKE 'event_scheduler'; 

如果显示off则开启事件:

set global event_scheduler = on;  

2.创建事件
CREATE EVENT 的语法如下:

CREATE EVENT
[IF NOT EXISTS] --------------------------------------------- 标注1
event_name -----------------------------------------------------标注2

ON SCHEDULE schedule ------------------------------------ 标注3
[ON COMPLETION [NOT] PRESERVE] -----------------标注4
[ENABLE | DISABLE] ----------------------------------------标注5
[COMMENT 'comment'] --------------------------------------标注6
DO sql_statement -----------------------------------------------标注7

例1:直接在事件中执行sql

CREATE EVENT delete_table_os_disk_history_31day
ON SCHEDULE
EVERY 1 DAY
STARTS NOW()
ON COMPLETION PRESERVE ENABLE
DO DELETE FROM mysql_status_history where TO_DAYS(NOW())-TO_DAYS(create_time)>8;

例2:–每隔一天自动调用e_test()存储过程

CREATE EVENT IF NOT EXISTS event_test
ON SCHEDULE EVERY 1 DAY
ON COMPLETION PRESERVE
DO CALL e_test();

3.开启事件

ALTER EVENT event_test ON 
COMPLETION PRESERVE ENABLE;

others

查询7天内的数据:
SELECT * FROM dubbo_invoke where TO_DAYS(NOW())-TO_DAYS(invoke_date)<7;
查询大于7天的数据:
SELECT * FROM dubbo_invoke where TO_DAYS(NOW())-TO_DAYS(invoke_date)<7;
删除7天以外的数据:
DELETE FROM dubbo_invoke where TO_DAYS(NOW())-TO_DAYS(invoke_date)>7;
以上时间可以任意修改。

4.事件生命周期管理相关:

关闭事件:

alter event insert_name on completion preserve disable;

删除事件:

drop event insert_name;

查看所有事件:

use dbname;
show events;

查看事件语句:

show create event event_name; 

例子:

如果event 执行不了,可能是因为event功能没有打开;

查看event是否打开:

show variables like '%scheduler%';

开启:

set global event_scheduler = 1;

 查看有哪些存储过程:

 show procedure status;

查看存储过程建立代码:

show create procedure proc_name;

发表评论

电子邮件地址不会被公开。