1.查询mysql数据库是否开启event事件支持
SHOW VARIABLES LIKE 'event_scheduler';
若显示为OFF,用管理员账号修改为开启:
set global event_scheduler = on;
2.创建需要被调用的存储过程
DELIMITER |
DROP PROCEDURE IF EXISTS number_unlock |
CREATE PROCEDURE number_unlock()
BEGIN
IF exists (select id from t_number_issue_detail where status='2' and SYSDATE() >= unlock_time) THEN
update t_number_issue_detail d left join t_number_issue_detail dd on d.id = dd.id set d.status ='1',d.unlock_time = null
where dd.status = '2' and SYSDATE() >= dd.unlock_time;
END IF;
END
|
DELIMITER;
注意:存储过程中的update语句查自身表中的数据作为条件修改自身表时,常规的update语句还是会报错:You can't specify target table 't_number_issue_detail' for update in FROM clause&#