Mysql中event任务调度，定时扫描数据，根据相关业务，更新数据

写在前面

这里记录下，Mysql 任务调度

处理场景：根据构建的初始数据，某条件，自动更新数据状态

一、代码示例

• 查看是否开启 event_scheduler
• 定义存储过程
• 定义 event_scheduler
• 启动 event_scheduler

详细如下

# 数据库系统管理，仅供参数设置，查询，业务数据，可另新建控制台
SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler = 1;

SELECT CURRENT_USER(), SCHEMA();

SHOW EVENTS from cadip_control;

SHOW PROCEDURE STATUS WHERE db = 'cadip_control' ;

SELECT
  routine_name,CREATED,LAST_ALTERED,ROUTINE_BODY,SQL_MODE,DTD_IDENTIFIER,ROUTINE_CATALOG,ROUTINE_SCHEMA,ROUTINE_TYPE
FROM
  information_schema.routines
WHERE
    routine_type = 'PROCEDURE'
  AND routine_schema = 'cadip_control';


SELECT SYSDATE(); -- -- 2020-05-10 12:14:13
SELECT NOW();  -- 2020-05-10 12:14:13

SELECT CURTIME() -- 12:13:43

-- 定义存储过程

DELIMITER |

DROP PROCEDURE IF EXISTS update_ope_status |

CREATE PROCEDURE update_ope_status ()
BEGIN

  IF EXISTS(
      SELECT info.system_id
      FROM operation_system_info info
      WHERE info.`status` = '0'
        AND SYSDATE() <= info.be_ready_date
    ) THEN
    UPDATE operation_system_info info
    SET info.`status` = '1'
    WHERE info.system_id IN (
      SELECT system_id
      FROM (
           SELECT system_id
           FROM operation_system_info
           WHERE STATUS = '0'
             AND SYSDATE() <= be_ready_date
         ) t
    );
  END IF ;
END |

DELIMITER ;



-- 创建定时器，每间隔 10 秒调用一次存储过程。
DELIMITER //
CREATE EVENT  event_ope_status
  ON SCHEDULE EVERY 10 second  do
  begin
    call update_ope_status();
  end //
DELIMITER ;


-- 启动定时器
ALTER EVENT event_ope_status  ON
  COMPLETION PRESERVE ENABLE;


-- 自定义调用存储过程
call update_ope_status();      

1.2、注意点

UPDATE operation_system_info info
    SET info.`status` = '1'
    WHERE info.system_id IN (
           SELECT system_id
           FROM operation_system_info
           WHERE STATUS = '0'
             AND SYSDATE() <= be_ready_date
    );      

UPDATE operation_system_info info
    SET info.`status` = '1'
    WHERE info.system_id IN (
      SELECT system_id
      FROM (
           SELECT system_id
           FROM operation_system_info
           WHERE STATUS = '0'
             AND SYSDATE() <= be_ready_date
         ) t
    );