sql ddl event

sql-ddl-Event(Structure Query Language Data Definition Language)

事件相关操作包含创建（Create），修改（Alert）
删除（Drop）

创建事件语句 CREATE EVENT

Statement

CREATE
EVENT
event_name
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
DO event_body;

CREATE
EVENT
event_insert_data
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
DO INSERT INTO t1(id,name) select max(id)+1,‘test’ from t1;

CREATE
EVENT
event_name
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
DO event_body;

schedule: {
AT timestamp [+ INTERVAL interval] …
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] …]
[ENDS timestamp [+ INTERVAL interval] …]
}
delimiter |

CREATE EVENT e_second_insert
ON SCHEDULE
EVERY 1 DAY
COMMENT ‘Saves total number of sessions then clears the table each day’
DO
BEGIN
INSERT INTO t1(id,name) select max(id)+1,‘test’ from t1;
END |

delimiter ;

定义一个触发器，实时统计添加日志。



查看视图，发现数据集也是变化的

触发器写的日志表中的数据也是变化的。

修改事件 ALTER EVENT Statement

ALTER
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT ‘string’]
[DO event_body]
delimiter |
CREATE EVENT e_second_insert
ON SCHEDULE
EVERY 1 MINUTE
COMMENT ‘insert data secondly’
DO
BEGIN
INSERT INTO t1(id,name) select max(id)+1,‘test’ from t1;
END |
delimiter ;

delimiter |
ALTER
EVENT e_second_insert
ON SCHEDULE
EVERY 1 MINUTE
COMMENT ‘insert changed from 1 second to 1 minute’
DO
BEGIN
INSERT INTO t1(id,name) select max(id)+1,‘EVERY 1 MINUTE’ from t1;
END |
delimiter ;

内容表里每分钟insert 条。

日志表里的时间

删除事件语句 DROP EVENT

Statement
DROP EVENT event_name;

DROP EVENT e_second_insert;

事件不在执行