Mysql创建定时任务

mysql查看存储过程

SHOW PROCEDURE STATUS;

查看event_scheduler

show events;

查看当前event_scheduler的状态

SHOW VARIABLES LIKE 'event_scheduler';

关闭event_scheduler

set GLOBAL event_scheduler=OFF;

删除event_scheduler

drop event event_name;

创建存储过程

-- 创建存储过程，用于每天生成一张新的历史表
DELIMITER $$
CREATE PROCEDURE create_daily_iothistory_table()
BEGIN-- 获取当前日期SET @today = DATE_FORMAT(NOW(), '%Y%m%d');-- 动态构建表名SET @table_name = CONCAT('commonService_iothistory_', @today);-- 检查表是否存在SET @sql = CONCAT('SHOW TABLES LIKE ''', @table_name, '''');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;-- 如果表不存在，则创建表IF ROW_COUNT() = 0 THENSET @sql = CONCAT('CREATE TABLE `', @table_name, '` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`dataAttributes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`dataAttributes`)),`createdAt` datetime(6) NOT NULL,`iot_id` bigint(20) NOT NULL,`correctAttributes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`correctAttributes`)),PRIMARY KEY (`id`),KEY `normal_iot_id` (`iot_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;END IF;END$$
DELIMITER ;-- 创建定时任务，每天执行存储过程
-- 具体实现方式依赖于您的数据库系统，例如使用MySQL的Event Scheduler
-- 例如：
-- CREATE EVENT IF NOT EXISTS daily_iothistory_event
-- ON SCHEDULE EVERY 1 DAY
-- DO CALL create_daily_iothistory_table();-- 每天凌晨执行存储过程
-- SET GLOBAL event_scheduler = ON; 
-- ALTER EVENT daily_iothistory_event ON SCHEDULE EVERY 1 DAY STARTS '2024-07-05 00:00:00'; -- 您可以根据需要调整定时任务的时间和执行频率