MySQL 定时任务

-- 1. MySQL evevt功能默认是关闭的,可以使用下面的语句来看evevt的状态，如果是OFF或者0，表示是关闭的。

   show VARIABLES LIKE '%sche%';

-- 2. 开启evevt功能
   
    SET GLOBAL event_scheduler = 1;





-- 1.1 创建 event 要调用的存储过程 test_status
	delimiter //
	drop procedure if exists auction_shop_status;
  create procedure auction_shop_status()
	begin 
	
		update auction_shop set auction_status = 1 where id in (select id from (select id from auction_shop where aution_start_time < CURRENT_TIMESTAMP()) ac);


	end;
  //
 delimiter ;



-- 查看存储过程
 show procedure status;



  -- 2.2 创建事件 auction_shop (其作用：每隔一秒自动调用 auction_shop_status()存储过程）

	drop event if exists auction_shop;
	create event auction_shop
	on schedule every 1 second
	on completion preserve disable
	do call auction_shop_status();


-- 3.3 开启事件

	alter event auction_shop on completion preserve enable;

  --  关闭事件 
  alter event auction_shop on completion preserve disable;

  --  查看事件
  select * from  mysql.event;