写在前面

这里记录下，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、注意点

要注意存储过程中的 sql 写法，不支持直接子查询，如下

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