MySQL事件调度器在每天的特定时间 [英] MySQL Event Scheduler on a specific time everyday

问题描述

这是我的查询

 创建事件重置在TIMESTAMP DO更新的日程表`ndic`.`students` SET`status` ='0'; 
  

如何每天下午1点将状态更新为0。我应该用什么代替TIMESTAMP？

解决方案

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

您有两个问题，首先，使事件重复，第二，使其每天13:00运行。

此示例创建一个循环事件。

  CREATE EVENT e_hourly 
 ON SCHEDULE 
 EVERY 1 HOUR 
 COMMENT'每小时清除会话表'
 DO 
 DELETE FROM site_activity.sessions; 
  

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

  SHOW EVENTS; 
  

它列出了每个事件及其元数据，如果它应该只运行一次，或者是重复出现。 / p>

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

通过尝试不同的表达方式，我们可以得出如下结果：

 创建事件如果不存在`session_cleaner_event` 
 ON SCHEDULE 
 EVERY 13 DAY_HOUR 
评论每天13:00清理会话！
 DO 
 DELETE FROM site_activity.sessions; 
  

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?

解决方案

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

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

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:

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;

这篇关于MySQL事件调度器在每天的特定时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！