如何在 MySQL 中调度存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6560639/
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
How to schedule a stored procedure in MySQL
提问by user455318
I have this stored procedure. How can I run this for example with intervals of 5 seconds? Like a routine for eliminate data with a time-stamp older than one day?
我有这个存储过程。例如，我如何以 5 秒的间隔运行它？像消除时间戳大于一天的数据的例程吗？
DROP PROCEDURE IF EXISTS `delete_rows_links`
GO
CREATE PROCEDURE delete_rows_links
BEGIN
DELETE activation_link
FROM activation_link_password_reset
WHERE TIMESTAMPDIFF(DAY, `time`, NOW()) < 1 ;
END
GO
回答by zerkms
You can use mysql scheduler to run it each 5 seconds. You can find samples at http://dev.mysql.com/doc/refman/5.1/en/create-event.html
您可以使用 mysql 调度程序每 5 秒运行一次。您可以在http://dev.mysql.com/doc/refman/5.1/en/create-event.html找到示例
Never used it but I hope this would work:
从未使用过它，但我希望这会起作用：
CREATE EVENT myevent
ON SCHEDULE EVERY 5 SECOND
DO
CALL delete_rows_links();
回答by Nero
I used this query and it worked for me:
我使用了这个查询，它对我有用：
CREATE EVENT `exec`
ON SCHEDULE EVERY 5 SECOND
STARTS '2013-02-10 00:00:00'
ENDS '2015-02-28 00:00:00'
ON COMPLETION NOT PRESERVE ENABLE
DO
call delete_rows_links();
回答by Rishi Gautam
In order to create a cronjob, follow these steps:
要创建 cronjob，请执行以下步骤：
run this command : SET GLOBAL event_scheduler = ON;
If ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL: mportant
运行此命令：SET GLOBAL event_scheduler = ON;
如果 ERROR 1229 (HY000): Variable 'event_scheduler' 是一个 GLOBAL 变量，应该用 SET GLOBAL 设置：重要
It is possible to set the Event Scheduler to DISABLED only at server startup. If event_scheduler is ON or OFF, you cannot set it to DISABLED at runtime. Also, if the Event Scheduler is set to DISABLED at startup, you cannot change the value of event_scheduler at runtime.
可以仅在服务器启动时将 Event Scheduler 设置为 DISABLED。如果 event_scheduler 为 ON 或 OFF，则不能在运行时将其设置为 DISABLED。此外，如果 Event Scheduler 在启动时设置为 DISABLED，则无法在运行时更改 event_scheduler 的值。
To disable the event scheduler, use one of the following two methods:
要禁用事件调度程序，请使用以下两种方法之一：
As a command-line option when starting the server:
--event-scheduler=DISABLEDIn the server configuration file (my.cnf, or my.ini on Windows systems): include the line where it will be read by the server (for example, in a [mysqld] section):
event_scheduler=DISABLEDRead MySQL documentationfor more information.
DROP EVENT IF EXISTS EVENT_NAME; CREATE EVENT EVENT_NAME ON SCHEDULE EVERY 10 SECOND/minute/hour DO CALL PROCEDURE_NAME();
作为启动服务器时的命令行选项：
--event-scheduler=DISABLED在服务器配置文件（my.cnf 或 Windows 系统上的 my.ini）中：包括服务器将读取的行（例如，在 [mysqld] 部分中）：
event_scheduler=DISABLED阅读 MySQL文档以获取更多信息。
DROP EVENT IF EXISTS EVENT_NAME; CREATE EVENT EVENT_NAME ON SCHEDULE EVERY 10 SECOND/minute/hour DO CALL PROCEDURE_NAME();
回答by dee-see
If you're open to out-of-the-DB solution: You could set up a cron job that runs a script that will itself call the procedure.
如果您对数据库外解决方案持开放态度：您可以设置一个运行脚本的 cron 作业，该脚本本身将调用该过程。
