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

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

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

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

CREATE EVENT IF NOT EXISTS event_user
ON SCHEDULE EVERY 5 SECOND
ON COMPLETION PRESERVE
COMMENT '新增用戶(hù)信息定時(shí)任務(wù)'
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;

--啟動(dòng)名稱(chēng)為event_user的事件。
ALTER EVENT event_user ENABLE;
--關(guān)閉名稱(chēng)為event_user的事件。
ALTER EVENT event_user DISABLE;

--刪除名稱(chēng)為event_user的事件。
DROP EVENT IF EXISTS event_user;

-- 單位有：SECOND,MINUTE,HOUR,DAY,WEEK(周),QUARTER(季度),MONTH,YEAR
-- 每秒執(zhí)行1次
ON SCHEDULE EVERY 1 SECOND
-- 每?jī)煞昼妶?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點(diǎn)50執(zhí)行
ON SCHEDULE AT '2021-12-01 1:50:00'
-- 5天后開(kāi)始每天都執(zhí)行執(zhí)行到下個(gè)月底
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
-- 每天凌晨一點(diǎn)執(zhí)行
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
-- 每個(gè)月的五號(hào)一點(diǎn)執(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)
-- 每年一月一號(hào)凌晨三點(diǎn)執(zhí)行一次
ON SCHEDULE  EVERY 1 YEAR STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE()) + 1,'-',1,'-',1)),INTERVAL 3 HOUR)

-- 查看定時(shí)策略是否開(kāi)啟
show variables like 'event_scheduler';
-- 開(kāi)啟定時(shí)策略（ON是開(kāi)啟狀態(tài)）
set global event_scheduler=1;
-- 創(chuàng)建procedure(存儲(chǔ)過(guò)程)
use 123;
delimiter //
create procedure updateTime()
begin
-- (begin后end//前放要定時(shí)處理的sql用;結(jié)尾，可以放多個(gè)sql)
update demo_table set demo_time = DATE_ADD(NOW(),  INTERVAL  FLOOR(1 + (RAND() * 10800))   SECOND );
end//
delimiter ;
-- 創(chuàng)建定時(shí)任務(wù)
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();
-- 查看定時(shí)任務(wù)事件
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
-- 開(kāi)啟和關(guān)閉創(chuàng)建好的事件
alter event updateTime_LY on completion preserve enable;//開(kāi)啟定時(shí)任務(wù)
alter event updateTime_LY on completion preserve disable;//關(guān)閉定時(shí)任務(wù)
-- 刪除
drop event updateTime_LY;