如何通过Event Scheduler实现10天后自动更新数据状态？

Hey there! Let's get that automatic status update sorted out for your barter_proposals table. I noticed a small tweak needed in your event code to make it work as intended.

First up, the issue with your current code: using sent_date=DATE_SUB(CURDATE(), INTERVAL 1 day) will only match records where sent_date is exactly one day ago. But what you need is to catch all records where the sent date is more than 10 days in the past (or 1 day for testing), so we'll swap the = for a < instead.

Test Version (Update after 1 day) #

Here's the adjusted code for your testing phase:

CREATE EVENT rot
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP -- Optional: sets when the event starts running
DO
  UPDATE barter_proposals
  SET proposal_status = "Aborted"
  WHERE sent_date < DATE_SUB(CURDATE(), INTERVAL 1 DAY)
    AND proposal_status = "Sent/Received"; -- Only update records that are still in this status

Production Version (Update after 10 days) #

Once testing checks out, just adjust the interval to 10 days:

CREATE EVENT rot
ON SCHEDULE EVERY 1 DAY
DO
  UPDATE barter_proposals
  SET proposal_status = "Aborted"
  WHERE sent_date < DATE_SUB(CURDATE(), INTERVAL 10 DAY)
    AND proposal_status = "Sent/Received";

Important Things to Keep in Mind #

• Enable MySQL Event Scheduler: If the event doesn't run, check if the scheduler is on. Run SET GLOBAL event_scheduler = ON; to enable it temporarily, or add event_scheduler = ON to your my.cnf/my.ini file for permanent activation.
• Date Format Check: If your sent_date is stored as a string (like dd/mm/yyyy), you'll need to convert it to a date type first for accurate comparisons. Use STR_TO_DATE(sent_date, '%d/%m/%Y') < DATE_SUB(CURDATE(), INTERVAL 10 DAY) instead of just sent_date.
• Prevent Duplicate Updates: The AND proposal_status = "Sent/Received" condition ensures we only update records that haven't already been marked as Aborted, so we don't waste resources on redundant updates.
• Adjust Execution Frequency: If daily runs are too slow or too frequent, you can change EVERY 1 DAY to something like EVERY 12 HOUR or EVERY 6 HOUR based on your needs.

内容的提问来源于stack exchange，提问作者chetan kambli