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)
);