需求
每天往一个表里面插入两条数据,但日期不同
INSERT INTO test(`id`, `art_training_institutions`, `places_for_performances`, `script_entertainment`, `video_game_hall`, `stage`, `ktv`, `Internet_bar`, `status`, `statistics_time`, `insert_time`) VALUES (100, 2253, 61, 546, 90, 51, 897, 1961, 1, '2022-08-24', '2022-08-25 14:00:00');INSERT INTO test(id`, `art_training_institutions`, `places_for_performances`, `script_entertainment`, `video_game_hall`, `stage`, `ktv`, `Internet_bar`, `status`, `statistics_time`, `insert_time`) VALUES (101, 0, 0, 0, 0, 0, 0, 0, 2, '2022-08-24', '2022-08-25 14:00:00');
如果没有其他调度或者不想每天自己手动执行,可以考虑使用MySQL自带的定时器来做
首先
1.查看是否开启定时策略
show variables like '%sche%';
如果你的是OFF
那就需要开启:
set global event_scheduler=1;
2.创建存储函数,存储定时执行的事件
根据自己的需求,修改存储函数
CREATE PROCEDURE insert_monitor()INSERT INTO test( `art_training_institutions`, `places_for_performances`, `script_entertainment`, `video_game_hall`, `stage`, `ktv`, `Internet_bar`, `status`, `statistics_time`)select 2253, 61, 546, 90, 51, 897, 1961, 1, CURRENT_DATEunion allselect 0, 0, 0, 0, 0, 0, 0, 2, CURRENT_DATE
3.创建定时任务
-- 每天0点执行create event if not EXISTS my_monitor on schedule every1 DAY STARTS date(CURRENT_DATE+1) on completion preservedo call insert_monitor();
4.查看创建的定时任务
show events;或者select * from mysql.event;
删除
drop event my_monitor
如果不想删除,可以考虑将它关闭
5.开启或关闭定时任务
-- 关闭alter event my_monitor on COMPLETION PRESERVE DISABLE;-- 开启alter event my_monitor on COMPLETION PRESERVE ENABLE;
补充:ON SCHEDULE
后面可以 自由发挥
- 每天凌晨一点执行定时任务
on schedule every1 DAY STARTS DATE_ADD(DATE(CURRENT_DATE+1), interval 1 hour)on schedule EVERY 1 DAY STARTS date_add(date( ADDDATE(curdate(),1)),interval 1 hour)
- 特定的日期特定的时间点执行定时任务
ON SCHEDULE at '2019-10-10 19:14:10'
- 每五分钟执行一次定时任务
ON SCHEDULE EVERY 5 MINUTE STARTS CURDATE()
- 每小时执行一次定时任务
ON SCHEDULE EVERY 1 HOUR STARTS CURDATE()
at
特定时间执行,every
重复执行
参考 MySQL – 定时任务(每天凌晨1点、每小时、每分钟、某一时间点)