First, check the switch:
SHOW VARIABLES LIKE 'event_scheduler'
If it's off:
SET GLOBAL event_scheduler = ON;
Second, create a stored procedure:
CREATE PROCEDURE update_test()
UPDATE test set num=num+1 where TIME_TO_SEC(TIMEDIFF(now(),createTime))>86400
Third, create an event to call the stored procedure:
CREATE EVENT update_event
ON SCHEDULE EVERY 2 SECOND STARTS NOW() ON COMPLETION PRESERVE DO
CALL update_test()
finished.
Demo1:
CREATE PROCEDURE update_test()
UPDATE Users SET UploadCount=UploadCount+1
CREATE EVENT update_event
ON SCHEDULE EVERY 2 SECOND STARTS NOW() ON COMPLETION PRESERVE DO
CALL update_test()
Demo2：
CREATE PROCEDURE update_test()
UPDATE Users SET UploadCount=10
CREATE EVENT update_event
ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP '2020-11-01 00:00:00' ON COMPLETION PRESERVE DO
CALL update_test()
Basically, you can understand this tutorial just by reading the demo.
But note that the sql server generally uses UTC time. If you want to correctly experience the superiority of scheduled tasks, don't forget to calculate the time difference between your local time and UTC time.
What you need to note is that mysql is case sensitive, especially table names. The USER table cited here is not the same table as the Users table I wrote myself.
If you want to check the execution of the scheduled task:
SELECT * FROM information_schema.EVENTS;
or
SELECT * FROM mysql.event;
or after switching to the corresponding database:
SHOW EVENTS;
The LAST_EXECUTED field will tell you the last execution time of the corresponding event.
If you want to delete the event：
drop event if exist update_test;
If you want to delete the procedure:
drop procedure if exist update_test
Tips:
86400 seconds=1day
604800 seconds=7days
The above scheduled tasks are executed once every 24 hours.