如何自动更新数据库中的记录? [英] How to auto update a record in database?

问题描述

我有一个作业表，在其中插入新的作业. 该表还包含职位发布日期.

I have a jobs table in which I inserts new jobs. This table also contains the job post date.

默认情况下，在进行新插入时，作业状态为打开.

By default the job status is open when a new insertion take place.

现在，我想在工作时间超过30天时将其状态从打开"更改为关闭".

Now I want to change the status of the job from open to close when the jobs becomes older than 30 days.

我将如何做?

推荐答案

尝试创建一个每天运行的事件，如下所示

Try creating a event which runs every day like below

CREATE EVENT myevent
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
  DO
    UPDATE my_table SET status ='closed'
    WHERE post_date > DATE_ADD(now(), INTERVAL -30 DAY)
    AND status='open'

-更新更改的语法

  CREATE EVENT myevent
  ON SCHEDULE EVERY 24 HOUR
  DO
    UPDATE my_table SET status ='closed'
    WHERE post_date > DATE_ADD(now(), INTERVAL -30 DAY)
    AND status='open'

这篇关于如何自动更新数据库中的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！