mysql event笔记

来源：互联网发布：python sftp上传文件编辑：程序博客网时间：2024/05/20 18:16

delimiter //
create EVENT my_stat_event
on SCHEDULE
EVERY 5 SECOND
on completion preserve ENABLE //这个选项用来确认事件在执行完毕后是否保存其定义,默认为不保存
do
begin
insert into steel2test.basic_bank(province_name) values('hehe');
end;

//
-------------------创建------------------------
CREATE EVENT test
     ON SCHEDULE EVERY 1 SECOND
     DO INSERT INTO test.aaa values(CURRENT_TIMESTAMP);

--------------------查看-----------------------
show events;

-------------------启用或者失效------------------------
启用或者使event失效
# --如果想启动单个事件，则执行下面命令
# ALTER EVENT myEvent
# ON COMPLETION PRESERVE
# ENABLE;
# --关闭 ALTER EVENT myEvent DISABLE;

--------------------删除-----------------------
DROP EVENT e6;或者DROP EVENT IF EXISTS e6;
-------------------------------------------
---------------------笔记-----------------------
1.在 MySQL 5.1.11中，它有3个值：0 (or OFF), 1 (or ON), or 2(默认值disable)
2.set global event_scheduler=on; /select @@EVENT_SCHEDULER;
3.EVENT权限是针对模式的(在MYSQL中也就是库的级别),不能对单独表来赋予权限。
4.通过在my.ini(windows)或者my.cnf(linux)中[mysqld]部分添加一条语句event_scheduler=on来达到mysql启动的时候加载event_scheduler.
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
event_scheduler=on