MySQL事件调度程序每天在特定时间

Here's my query

这是我的查询

CREATE EVENT reset  ON SCHEDULE AT TIMESTAMP  DO UPDATE `ndic`.`students` SET `status` =  '0';

How can I update status to "0" at 1 pm every day. What should I put instead of TIMESTAMP?

如何在每天下午1点将状态更新为“0”。我该怎么办而不是TIMESTAMP？

6 个解决方案

#1

The documentation on CREATE EVENT is quite good, but it takes a while to get it right.

关于CREATE EVENT的文档非常好，但需要一段时间才能正确完成。

You have two problems, first, making the event recur, second, making it run at 13:00 daily.

你有两个问题，第一，使事件重复，第二，使它在每天13:00运行。

This example creates a recurring event.

此示例创建一个周期性事件。

CREATE EVENT e_hourly
    ON SCHEDULE
      EVERY 1 HOUR
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;

When in the command-line MySQL client, you can:

在命令行MySQL客户端中，您可以：

SHOW EVENTS;

This lists each event with its metadata, like if it should run once only, or be recurring.

这会列出每个事件及其元数据，例如它应该只运行一次，还是重复发生。

The second problem: pointing the recurring event to a specific schedule item.

第二个问题：将定期事件指向特定的计划项目。

By trying out different kinds of expression, we can come up with something like:

通过尝试不同类型的表达，我们可以提出如下：

CREATE EVENT IF NOT EXISTS `session_cleaner_event`
ON SCHEDULE
  EVERY 13 DAY_HOUR
  COMMENT 'Clean up sessions at 13:00 daily!'
  DO
    DELETE FROM site_activity.sessions;

#2

This might be too late for your work, but here is how I did it. I want something run everyday at 1AM - I believe this is similar to what you are doing. Here is how I did it:

这对你的工作来说可能为时已晚，但这就是我如何做到的。我希望每天凌晨1点运行一些东西 - 我相信这与你正在做的类似。我是这样做的：

CREATE EVENT event_name
  ON SCHEDULE
    EVERY 1 DAY
    STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY + INTERVAL 1 HOUR)
  DO
    # Your awesome query

#3

My use case is similar, except that I want a log cleanup event to run at 2am every night. As I said in the comment above, the DAY_HOUR doesn't work for me. In my case I don't really mind potentially missing the first day (and, given it is to run at 2am then 2am tomorrow is almost always the next 2am) so I use:

我的用例类似，只是我希望每天凌晨2点运行日志清理事件。正如我在上面的评论中所说，DAY_HOUR对我不起作用。在我的情况下，我真的不介意可能错过第一天（并且，鉴于它是在凌晨2点运行，明天凌晨2点几乎总是下一个凌晨2点）所以我使用：

CREATE EVENT applog_clean_event
ON SCHEDULE 
    EVERY 1 DAY
    STARTS str_to_date( date_format(now(), '%Y%m%d 0200'), '%Y%m%d %H%i' ) + INTERVAL 1 DAY
COMMENT 'Test'
DO

#4

DROP EVENT IF EXISTS xxxEVENTxxx;
CREATE EVENT xxxEVENTxxx
  ON SCHEDULE
    EVERY 1 DAY
    STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY + INTERVAL 1 HOUR)
  DO
    --process;

¡IMPORTANT!->

重要： - >

SET GLOBAL event_scheduler = ON;

#5

Try this

尝试这个

CREATE EVENT event1
ON SCHEDULE EVERY '1' DAY
STARTS '2012-04-17 13:00:00' -- should be in the future
DO
-- your statements
END

#6

CREATE EVENT test_event_03
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
   INSERT INTO messages(message,created_at)
   VALUES('Test MySQL recurring Event',NOW());

#1