mysql 定时事件（event）

mysql创建事件，并且定时运行

-- 查看当前数据库中已存在的所有事件
SHOW EVENTS;
SELECT * FROM information_schema.events;

select now()

-- 创建一个每天上午9点运行的定时事件
CREATE EVENT event_update_userstatus
ON SCHEDULE
  EVERY 1 DAY
  STARTS TIMESTAMP(CURRENT_DATE, '9:00:00')
DO
BEGIN
  -- 在这里放入你要执行的SQL语句
  update  mdmemployee set IsDelete=1
	,stdIsDeleted=1
	,modifiedTime=NOW()
	,ModifyDate=NOW()
	where IsDelete=0 and UserId IN
	(
		SELECT UserId from mdmrecordinfo 
		where isdelete=0 and employeeStatus in (6,8)
	);
END;

	
	
-- 删除事件
DROP EVENT IF EXISTS event_update_userstatus;

-- 启用事件计划
SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler = ON;

-- 校验
SELECT UserId from mdmrecordinfo where isdelete=0 and employeeStatus in (6,8)
select IsDelete from mdmemployee where UserId='178008096'
-- update mdmemployee set IsDelete=0 where UserId='178008096'

select * from mdmemployee
where IsDelete=0 and UserId IN
(
	SELECT UserId from mdmrecordinfo 
	where isdelete=0 and employeeStatus in (6,8)
);