SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;
SHOW PROCESSLIST;

-- 開啟事件調(diào)度器
SET GLOBAL event_scheduler = ON;
-- 關閉事件調(diào)度器
SET GLOBAL event_scheduler = OFF;
-- 查看事件調(diào)度器狀態(tài)
SHOW VARIABLES LIKE 'event_scheduler';

 事件調(diào)度器啟動狀態(tài)
event_scheduler = on

-- 創(chuàng)建用戶信息表
CREATE TABLE IF NOT EXISTS tb_user
(
	id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用戶編號',
	name VARCHAR(30) NOT NULL COMMENT '用戶姓名',
	create_time TIMESTAMP COMMENT '創(chuàng)建時間'
) COMMENT = '用戶信息表';

CREATE EVENT IF NOT EXISTS event_user
ON SCHEDULE EVERY 5 SECOND
ON COMPLETION PRESERVE
COMMENT '新增用戶信息定時任務'
DO INSERT INTO tb_user(name,create_time) VALUES('pan_junbiao的博客',NOW());

CREATE PROCEDURE p_total()
BEGIN
	DECLARE n_total INT default 0;
	SELECT COUNT(*) INTO n_total FROM db_database11.tb_user;
	INSERT INTO tb_total (userNumber,createtime) VALUES(n_total,NOW());
END;

CREATE EVENT IF NOT EXISTS e_autoTotal
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL 1 MONTH),INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE
DO CALL p_total();

SELECT * FROM information_schema.events; 

ALTER
	[DEFINER={user | CURRENT_USER}]
	EVENT [IF NOT EXISTS] event_name
	ON SCHEDULE schedule
	[ON COMPLETION [NOT] PRESERVE]
	[ENABLE | DISABLE | DISABLE ON SLAVE]
	[COMMENT 'comment']
	DO event_body;

--啟動名稱為event_user的事件。
ALTER EVENT event_user ENABLE;
--關閉名稱為event_user的事件。
ALTER EVENT event_user DISABLE;

--刪除名稱為event_user的事件。
DROP EVENT IF EXISTS event_user;

-- 單位有：SECOND,MINUTE,HOUR,DAY,WEEK(周),QUARTER(季度),MONTH,YEAR
-- 每秒執(zhí)行1次
ON SCHEDULE EVERY 1 SECOND
-- 每兩分鐘執(zhí)行1次
ON SCHEDULE EVERY 2 MINUTE
-- 每3天執(zhí)行1次
ON SCHEDULE EVERY 3 DAY
-- 5天后執(zhí)行
ON SCHEDULE AT CURRENT_TIMESTAMP()+INTERVAL 5 DAY
-- 10分鐘后執(zhí)行
ON SCHEDULE AT CURRENT_TIMESTAMP()+INTERVAL 10 MINUTE
-- 在2016年10月1日，晚上9點50執(zhí)行
ON SCHEDULE AT '2021-12-01 1:50:00'
-- 5天后開始每天都執(zhí)行執(zhí)行到下個月底
ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP()+INTERVAL 5 DAY ENDS CURRENT_TIMESTAMP()+INTERVAL 1 MONTH
-- 從現(xiàn)在起每天執(zhí)行，執(zhí)行5天
ON SCHEDULE EVERY 1 DAY ENDS CURRENT_TIMESTAMP()+INTERVAL 5 DAY
-- 每天凌晨一點執(zhí)行
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
-- 每個月的五號一點執(zhí)行一次
ON SCHEDULE EVERY 5 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)
-- 每年一月一號凌晨三點執(zhí)行一次
ON SCHEDULE  EVERY 1 YEAR STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE()) + 1,'-',1,'-',1)),INTERVAL 3 HOUR)

-- 查看定時策略是否開啟
show variables like 'event_scheduler';
-- 開啟定時策略（ON是開啟狀態(tài)）
set global event_scheduler=1;
-- 創(chuàng)建procedure(存儲過程)
use 123;
delimiter //
create procedure updateTime()
begin
-- (begin后end//前放要定時處理的sql用;結尾，可以放多個sql)
update demo_table set demo_time = DATE_ADD(NOW(),  INTERVAL  FLOOR(1 + (RAND() * 10800))   SECOND );
end//
delimiter ;
-- 創(chuàng)建定時任務
create event updateTime_LY
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
on completion preserve disable
do call updateTime();
-- 查看定時任務事件
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
-- 開啟和關閉創(chuàng)建好的事件
alter event updateTime_LY on completion preserve enable;//開啟定時任務
alter event updateTime_LY on completion preserve disable;//關閉定時任務
-- 刪除
drop event updateTime_LY;