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());

1秒あたりのスケジュール

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

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;