Mysql创建定时计划任务调用存储过程或函数

应用场景：定时获取SQL结果存入新表作为报表查询，提升查询性能

查看是否开启事件计划

show variables like '%event_sche%';

开启事件计划，有以下两种方法

在命令行执行语句修改，这种方式虽然能生效，但是会在数据库重启后恢复原来的状态

set global event_scheduler=1;

修改my.ini配置文件，使配置在数据库重启后依然生效，修改后记得要重启数据库

[mysqld]
event_scheduler=ON

创建表

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `dept` tinyint NOT NULL COMMENT '部门id',
  `age` tinyint NOT NULL COMMENT '年龄',
  `name` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户名称',
  `create_time` datetime NOT NULL COMMENT '注册时间',
  `last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='测试表';

创建存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `test`()
BEGIN
DECLARE i INT;

    SET i = (SELECT COALESCE(MAX(id), 1) FROM `user`); -- 初始化 i

    IF i = 1 THEN
        INSERT INTO `user`(dept, age, name, create_time, last_login_time)
        VALUES (1, 25, 'user_1', '2018-01-01 00:00:00', '2018-03-01 12:00:00');
    ELSE
        INSERT INTO `user`(dept, age, name, create_time, last_login_time)
        SELECT 
            FLOOR(RAND() * 10) AS dept,
            FLOOR(20 + RAND() * (50 - 20 + 1)) AS age,
            CONCAT('user_', i + 1) AS name,
            DATE_ADD('2018-01-01 00:00:00', INTERVAL (i + 1) * FLOOR(RAND() * 100) SECOND) AS create_time,
            DATE_ADD(DATE_ADD('2018-01-01 00:00:00', INTERVAL (i + 1) * FLOOR(RAND() * 100) SECOND), INTERVAL FLOOR(RAND() * 1000000) SECOND) AS last_login_time
        FROM `user`;
            -- (SELECT 1 AS dummy) AS t;  -- 生成一行数据
    END IF;
END

手动调用存储过程

call user_procedure();

创建定时任务，定时执行

-- 查看现有的定时任务
select * from information_schema.`EVENTS`;

-- 开启或关闭定时任务
alter event test_event on completion preserve enable; -- 开启定时任务
alter event test_event on completion preserve DISABLE; -- 开启定时任务

-- 创建定时任务
-- 先删除事件 如果有多个时间，注意区分，不要删除了别人的事件
DROP EVENT IF EXISTS test_event ;
CREATE EVENT `test_event` -- 创建名字为test_event的事件
ON SCHEDULE EVERY 5 SECOND STARTS DATE_ADD(now(), INTERVAL 1 MINUTE) -- 每隔5秒执行一次，开始执行时间为当前时间的下一分钟
ON COMPLETION NOT PRESERVE
ENABLE
DO call test(); -- 指定要执行的存储过程
-- 参数释义
-- SCHEDULE EVERY 5 SECOND 计划 每 5 [秒] 执行  也可以使用分钟[MINUTE]和天[DAY]
-- STARTS DATE_ADD(now(), INTERVAL 1 MINUTE) 告诉她什么时候开始执行 这个函数得到的是，当前时间的下一分钟，也可以直接指定具体时间开始 '2025-04-10 12:00:02'

创建定时任务，使用时锁，让函数串行

DROP EVENT IF EXISTS test_event ;	
CREATE EVENT `test_event`	-- 创建名字为user_event的事件
ON SCHEDULE EVERY 5 SECOND STARTS DATE_ADD(now(), INTERVAL 1 MINUTE) -- 每隔一分钟执行一次，开始执行时间为当前时间的下一分钟
ON COMPLETION NOT PRESERVE
ENABLE
DO BEGIN
    DECLARE lock_acquired BOOLEAN;
    -- 尝试获取锁，超时时间可以调整
    SET lock_acquired = GET_LOCK('fun_test', 0);

    IF lock_acquired THEN
        -- 调用你的函数
        CALL test();
        -- 释放锁
        DO RELEASE_LOCK('fun_test');
    ELSE
        -- 如果无法获得锁，表示函数正在执行
        SELECT 'Function is already running, skipping this run.';
    END IF;
END;

创建计划任务或存储过程，使用表数据控制，让函数串行

-- 创建一个状态表
CREATE TABLE job_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    is_running BOOLEAN DEFAULT FALSE
);

-- 定义事件
CREATE EVENT my_event
ON SCHEDULE EVERY 5 MINUTE
DO
BEGIN
    DECLARE running BOOLEAN;
    -- 检查是否有其他任务正在执行
    SELECT is_running INTO running FROM job_status WHERE id = 1;
    IF running = FALSE THEN
        -- 设定状态为正在执行
        UPDATE job_status SET is_running = TRUE WHERE id = 1;
        -- 调用你的函数
        CALL your_function();
        -- 设定状态为完成
        UPDATE job_status SET is_running = FALSE WHERE id = 1;
    ELSE
        -- 如果正在执行，可以选择记录日志或其他处理
        SELECT 'Function is already running, skipping this run.';
    END IF;
END;