每天特定时间的 MySQL 事件调度程序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3070277/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Event Scheduler on a specific time everyday
提问by gin
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？
回答by
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
回答by olleolleolle
The documentation on CREATE EVENTis 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;
回答by Adam
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
回答by Naveen Kumar
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
回答by mercu
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;
回答by aymen messaoudi
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());
