MySQL事件管理 event和view

开启事件调度器

通过命令行

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

通过配置文件my.cnf

event_scheduler = 1 #或者ON

调度器应用

立即调度

create table ev1( 
ev_name varchar(20) not null,
ev_started timestamp not null);

create event event_now
on schedule
at now()
do insert into ev1 values('ev_test', now());

每分钟调度

create event ev2
on schedule
every 1 minute
do insert into ev1 values('ev_test1', now());

每秒调度

CREATE event ev3
ON SCHEDULE
EVERY 1 SECOND
DO INSERT INTO ev3 VALUES(1);

每秒调度存储过程

CREATE DEFINER=`root`@`localhost` EVENT `eventUpdateStatus`
ON SCHEDULE EVERY 1 SECOND
STARTS '2017-11-21 00:12:44'
ON COMPLETION PRESERVE
ENABLE
DO call updateStatus()

过程式创建 events

DELIMITER $$
//事件的名称
CREATE EVENT `test`
//60秒循环一次
ON SCHEDULE EVERY 60 MINUTE_SECOND
// 开始时间,结束时间
STARTS '2017-11-01 00:00:00.000000' ENDS '2017-11-30 00:00:00.000000'
//过期后禁用事件而不删除
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
//执行的内容
insert into ev1 values('event_now', now());
insert into ev1 values('event_now1', now());
ENDR $$
DELIMITE ;

视图

语法

mysql> create view as select column from table_name where condition;

举例

create view v_city
as
select a.name as aname ,b.name as bname ,a.population ,b.surfacearea
from city as a
join country as b where a.population<100;
select * from v_city;