Mysql event示例

貼上示例SQL

-- 檢視系統事件相關參數
show variables like '%event%';

show EVENTS;

-- 事件開啟、關閉：
ALTER EVENT myEvent1 ON COMPLETION PRESERVE ENABLE;
ALTER EVENT myEvent2 ON COMPLETION PRESERVE DISABLE;


-- 檢視排程器線程
show processlist;

-- Events 操作執行個體

create table t2(id int auto_increment primary key,t_time datetime);

-- 以下為兩種方式事件
-- 執行一次和周期執行

-- 立即啟動，執行一次
CREATE EVENT `pis`.`Untitled`
ON SCHEDULE AT '2000-02-02 00:00:00'
DO SELECT * FROM mysql;

CREATE EVENT `pis`.`Untitled`
ON SCHEDULE AT '2000-02-02 00:00:00' + INTERVAL '5' DAY
DO SELECT * FROM mysql;

		
-- 周期執行
-- navicate 這裡為什麼無法加入截止時間?，有答案的可以分享一下
CREATE DEFINER = root EVENT `pis`.`Untitled`
ON SCHEDULE
EVERY '5' HOUR STARTS '2000-02-03 11:19:16'
DO SELECT * FROM mysql;

CREATE DEFINER = root EVENT `pis`.`Untitled`
ON SCHEDULE
EVERY '5' HOUR STARTS '2030-12-03 11:19:16' + INTERVAL '2' DAY
DO SELECT * FROM mysql;