开启事件调度器
通过命令行
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;