How Can I Use MySQL\'s Event Scheduler to Automate Daily Database Updates?

Daily MySQL Event Scheduler

In MySQL, an event scheduler can be used to automate certain tasks on a regular basis. One common scenario is to update database records at a specific time each day.

Creating an Event to Update Status Daily

To update the status field in the students table to "0" at 1 pm every day, you can use the following event creation query:

CREATE EVENT update_status_daily
ON SCHEDULE
EVERY 1 DAY
STARTS CAST('2023-05-01 13:00:00' AS DATETIME)
DO
  UPDATE `ndic`.`students`
  SET `status` = '0';
END

Alternative to TIMESTAMP

In the query above, the event is triggered based on a specific timestamp ('2023-05-01 13:00:00'). However, you can also use other time-based expressions instead of a timestamp.

One option is to use the EVERY clause with a DAY interval:

ON SCHEDULE
EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 13 HOUR

This will trigger the event at 1 pm on the following day (1 day plus 13 hours from the current time).

Another option is to use the AT clause with a time value:

ON SCHEDULE
AT '13:00:00'

This will trigger the event at 1 pm on the current day.

The above is the detailed content of How Can I Use MySQL\'s Event Scheduler to Automate Daily Database Updates?. For more information, please follow other related articles on the PHP Chinese website!