Cronjob 或 MySQL 事件？

Cronjob or MySQL event?

提问by Michael Eilers Smith

I have to update my MySQL database every hour, and I was wondering what the advantages/disadvantages of using a cronjob VS a MySQL event? For example, which is faster? Which is safer? Thanks!

我必须每小时更新我的​​ MySQL 数据库，我想知道使用 cronjob VS MySQL 事件的优点/缺点是什么？例如，哪个更快？哪个更安全？谢谢！

采纳答案by Bohemian

I would always go a cron job, because:

我总是会去做一份 cron 工作，因为：

• That's where sysadmins will expect it to be (this point is not to be underestimated)
• crobtab is bullet-proof, time-tested, extremely widely used and understood
• You can freely direct/analyse error/success messages where you want
• Some database tasks require/prefer mysql to be off-line (eg full backup), so you've got to use cron for those - it's a bad idea to have some tasks done with cron and some done with mysql; you'll be unsure where to look
• You can chain up other events that should follow if you've got a shell script

• 这就是系统管理员所期望的（这一点不可低估）
• crobtab 是防弹的、经过时间考验的、使用极其广泛和理解的
• 您可以自由地在您想要的地方直接/分析错误/成功消息
• 一些数据库任务需要/更喜欢 mysql 离线（例如完整备份），所以你必须使用 cron 来完成这些任务 - 用 cron 完成一些任务而用 mysql 完成一些任务是个坏主意；你会不确定去哪里看
• 如果你有一个 shell 脚本，你可以链接其他应该遵循的事件

And finally, just because you cando something, doesn't mean it's a good idea. Mysql is good at data stuff. Don't use it for "shell" stuff.

最后，仅仅因为你可以做某事，并不意味着这是个好主意。Mysql 擅长数据的东西。不要将它用于“外壳”的东西。

回答by user2001117

MySQL Event Scheduler – A good replacement for cron.

MySQL Event Scheduler – 一个很好的 cron 替代品。

We all know about cron, an easy way to schedule certain processes, like truncating your log tables in your MySQL database every week.

我们都知道 cron，这是一种安排特定进程的简单方法，例如每周截断 MySQL 数据库中的日志表。

With MySQL 5.1 the guys at MySQL introduced a new cool feature: The MySQL Event Scheduler !

在 MySQL 5.1 中，MySQL 的家伙们引入了一个很酷的新特性：MySQL 事件调度器！

With the Event Scheduler you can schedule tasks that you want to perform on your database. This is great for web developers who can't create cron jobs on their webspace, because their host won't let them! It really is a great replacement for cron!

使用 Event Scheduler，您可以安排要在数据库上执行的任务。这对于无法在其网站空间上创建 cron 作业的 Web 开发人员来说非常有用，因为他们的主机不会让他们这样做！它真的是 cron 的绝佳替代品！

A few examples:

几个例子：

you want to truncate your application log table every week, this is how your event schedule should look like:

你想每周截断你的应用程序日志表，这就是你的事件时间表应该是这样的：

CREATE EVENT PurgeLogTable
ON SCHEDULE EVERY 1 WEEK
DO
BEGIN
DELETE FROM `logs` WHERE `LogTime` <= DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 1 WEEK);
INSERT INTO `audit` (`AuditDate`, `Message`) VALUES(NOW(), "Log table purged succesfully!");
END

回答by Suresh Kamrushi

Mysql introduce Event scheduler which we can use alternative to Cronjob. There are many advantages over cronjob like:

Mysql 引入了事件调度器，我们可以使用它来替代 Cronjob。与 cronjob 相比有很多优点，例如：

1)It is directly written on Mysql Server.

1）直接写在Mysql Server上。

2) This is platform independent. Your application might be written in any language it does not matters. You just need to know mysql.

2）这是平台无关的。您的应用程序可能是用任何语言编写的，这无关紧要。你只需要知道mysql。

3) We can use them whenever there is a database update or cleanup required at regulare interval.

3) 我们可以在需要定期更新或清理数据库时使用它们。

4) No need to compile queries every time hence performace increased.

4) 无需每次都编译查询，从而提高了性能。

5) Error can be log in log files. Syntax:

5）错误可以登录日志文件。句法：

DELIMITER //
CREATE EVENT eventName
ON SCHEDULE EVERY 1 WEEK
STARTS 'Some Date to start'
ENDS 'End date If any' 

DO
BEGIN
   // Your query will be here
END//
DELIMITER ;

For more information you can visit official site:http://dev.mysql.com/doc/refman/5.1/en/create-event.html

更多信息可以访问官方网站：http: //dev.mysql.com/doc/refman/5.1/en/create-event.html

detail blog: http://goo.gl/6Hzjvg

详细博客：http: //goo.gl/6Hzjvg

回答by meet

i am going with mysql event schedular

我要使用 mysql 事件调度程序

because we don't have to code one extra file for it, our purpose can be fulfilled by just writing one query.

因为我们不必为它编写一个额外的文件，我们的目的可以通过编写一个查询来实现。

if only database related operation required then mysql event schedular is good choice.

如果只需要与数据库相关的操作，那么mysql event schedular 是不错的选择。

回答by Barend Scholtus

I'm working with EVENTs myself now and pondered the same :)

我现在正在与 EVENTs 一起工作并思考同样的问题:)

In addition to the above answers:

除了以上答案：

• Use EVENTs if the task is purely data-centric, or complements functionality that is already build into the database. You may already have triggers that clean up data, log certain events, aggregate certain data, etc. If the scheduled task you want to perform is part of the existing design, it's more cohesive to trigger the task from an EVENT. EVENTs are temporal triggersafter all.

• If you run a script from a shell, you need a username/password stored in the shell script or in a defaults file. For executing EVENTs, you don't need a username/password after the EVENT has been created.

• IMCO you should write the logic in a stored procedure; then invoke the procedure from an EVENT or from a shell script, whatever suits you best. You might even build some place in your UI that enables users to invoke the procedure manually.

• 如果任务完全以数据为中心，或者补充已经内置到数据库中的功能，请使用 EVENT。您可能已经拥有清理数据、记录某些事件、聚合某些数据等的触发器。如果您要执行的计划任务是现有设计的一部分，那么从 EVENT 触发任务会更有凝聚力。毕竟，事件是时间触发器。

• 如果从 shell 运行脚本，则需要在 shell 脚本或默认文件中存储用户名/密码。要执行 EVENT，在创建 EVENT 后不需要用户名/密码。

• IMCO 您应该在存储过程中编写逻辑；然后从 EVENT 或 shell 脚本调用该过程，无论哪个最适合您。您甚至可以在 UI 中构建一些位置，使用户能够手动调用该过程。

回答by Christopher Pelayo

This have been a long time question but I guess one advantage of using mysql events is its directly embedded within the dbms so it does not need to create/maintain a connection if for example you are using php as your main language you could also consider this cause with that you eliminate the necessity for validation upon creating the connection to the db before being able to run a statement and also you can create an event using a role that could do maintenance scripts and assign a user role to the application that does not that will give you security that you know that users only have this kind of role could access so deciding whether to use events or cron job depends on the system your working on. But another thing to watch out is multiple trigger of the same event especially if the interval is just a difference is just a matter of seconds like every 5-10secs etc.

这是一个很长时间的问题，但我想使用 mysql 事件的一个优点是它直接嵌入在 dbms 中，因此它不需要创建/维护连接，例如，如果您使用 php 作为主要语言，您也可以考虑这个这样做的原因是，在能够运行语句之前，您无需在创建与数据库的连接时进行验证，而且您还可以使用可以执行维护脚本的角色创建事件，并将用户角色分配给不这样做的应用程序将为您提供安全性，您知道用户只有这种角色才能访问，因此决定是使用事件还是 cron 作业取决于您使用的系统。但另一件要注意的事情是同一事件的多次触发，特别是如果间隔只是一个差异，那么每隔 5-10 秒等只是几秒钟的问题。

回答by CoolRunning

Mysql event scheduler is on the advantage for shared host environment. In terms of timing, it can also be scheduled down to the second. Unlike cron, it's by the minute - but you can work around this limitation however.

Mysql 事件调度程序在共享主机环境中具有优势。在时间上，也可以安排到秒。与 cron 不同，它是按分钟计算的 - 但是您可以解决此限制。