mysql删除事件的使用

mysql删除事件的使用

MySQL events were added in MySQL 5.1.6 and offer an alternative to scheduled tasks and cron jobs. Events can be used to create backups, delete stale records, aggregate data for reports, and so on. Unlike standard triggers which execute given a certain condition, an event is an object that is triggered by the passage of time and is sometimes referred to as a temporal trigger. You can schedule events to run either once or at a recurring interval when you know your server traffic will be low.

MySQL事件已添加到MySQL 5.1.6中，并提供了计划任务和cron作业的替代方法。 事件可用于创建备份，删除陈旧记录，汇总报告数据等等。 与在特定条件下执行的标准触发器不同，事件是随时间流逝而触发的对象，有时也称为时间触发器 。 当您知道服务器流量较低时，可以将事件安排为一次运行或以周期性间隔运行。

In this article I’ll explain what you need to know to get started using events: starting the event scheduler, adding events to run once or multiple times, viewing existing events, and altering events. I’ll also share with how you might use MySQL events using scheduled blog posts as a practical example.

在本文中，我将解释使用事件开始时需要了解的内容：启动事件调度程序，添加事件以运行一次或多次，查看现有事件以及更改事件。 我还将与您分享如何通过计划的博客文章来使用MySQL事件，这是一个实际示例。

启动事件计划程序 (Starting the Event Scheduler)

The MySQL event scheduler is a process that runs in the background and constantly looks for events to execute. Before you can create or schedule an event, you need to first turn on the scheduler, which is done by issuing the following command:

MySQL事件调度程序是在后台运行并不断寻找要执行的事件的进程。 在创建或安排事件之前，需要首先打开计划程序，这是通过发出以下命令来完成的：

mysql> SET GLOBAL event_scheduler = ON;

Likewise, to turn all events off you would use:

同样，要关闭所有事件，可以使用：

mysql> SET GLOBAL event_scheduler = OFF;

Once the event scheduler is started, you can view its status in MySQL’s process list.

启动事件计划程序后，您可以在MySQL的进程列表中查看其状态。

mysql> SHOW PROCESSLISTG ... Id: 79 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 12 State: Waiting on empty queue Info: NULL

处理事件 (Working with Events)

It’s important to note that when an event is created it can only perform actions for which the MySQL user that created the event has privileges to perform. Some additional restrictions include:

重要的是要注意，创建事件时，它只能执行创建该事件MySQL用户有权执行的操作。 其他一些限制包括：

Event names are restricted to a length of 64 characters.

事件名称的长度不能超过64个字符。 As of MySQL 5.1.8, event names are not case-sensitive; each event name should be unique regardless of case.

从MySQL 5.1.8开始，事件名称不区分大小写。 无论大小写，每个事件名称都应该唯一。 Events cannot be created, altered, or dropped by another event.

不能通过其他事件创建，更改或删除事件。

You cannot reference a stored function or user-defined function when setting the event schedule.

设置事件时间表时，不能引用存储的功能或用户定义的功能。

建立活动 (Creating Events)

The following example creates an event:

以下示例创建一个事件：

DELIMITER | CREATE EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO BEGIN UPDATE mytable SET mycol = mycol + 1; END | DELIMITER ;

This event will run once, one hour from the time it was created. The BEGIN and END statements surround one or multiple queries which will be executed at the specified time. Because the semicolon is needed to terminate the UPDATE statement, you’ll need to switch delimiters before you issue the CREATE EVENT statement and then switch back afterwards if you’re working through a client.

该事件将在创建后的一小时内运行一次。 BEGIN和END语句包围将在指定时间执行的一个或多个查询。 因为需要用分号来终止UPDATE语句，所以您需要在发出CREATE EVENT语句之前切换定界符，然后在通过客户端工作时再切换回来。

You can view a list of all existing events with SHOW EVENTS.

您可以使用SHOW EVENTS查看所有现有事件的列表。

mysql> SHOW EVENTSG ********************** 1. row ********************** Db: mysql Name: myevent Definer: dbuser@localhost Time zone: SYSTEM Type: ONE TIME Execute At: 2011-10-26 20:24:19 Interval Value: NULL Interval Field: NULL Starts: NULL Ends: NULL Status: ENABLED Originator: 0 character_set_client: utf8 collation_connection: utf8_general_ci

After an event has expired it will be automatically deleted unless you explicitly stated otherwise with an ON COMPLETION clause, for example:

事件过期后，它将自动删除，除非您另外使用ON COMPLETION子句明确声明，例如：

CREATE EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR ON COMPLETION PRESERVE DO BEGIN UPDATE mytable SET mycol = mycol + 1; END |

In this example, even though the event has expired it will still be retained in the database which will allow you to alter and run it again later, or perhaps you’d just like to keep it for reference.

在此示例中，即使事件已过期，它仍将保留在数据库中，这将允许您更改并稍后再次运行它，或者您只是想保留它以供参考。

To permanently delete an event yourself, you can use DROP EVENT:

要自己永久删除一个事件，可以使用DROP EVENT ：

DROP EVENT myevent;

To specify a recurring event, you would use the EVERY clause:

要指定重复发生的事件，可以使用EVERY子句：

CREATE EVENT myevent ON SCHEDULE EVERY 1 HOUR DO BEGIN UPDATE mytable SET mycol = mycol + 1; END |

And rather than having an event that just runs once or forever, you can also schedule a reoccurring event that is valid only within a specific time period, using START and END clauses:

而且，除了让事件仅运行一次或永远不会发生之外，您还可以使用START和END子句安排仅在特定时间段内有效的重复发生的事件：

CREATE EVENT myevent ON SCHEDULE EVERY 1 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY ENDS CURRENT_TIMESTAMP + INTERVAL 1 YEAR DO BEGIN UPDATE mytable SET mycol = mycol + 1; END |

In this example, the reoccurring event would start tomorrow and continue to run every hour for a full year.

在此示例中，重复发生的事件将从明天开始，并持续整整一年每小时运行一次。

With regard to timing, the interval specified can be YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND. Keep in mind that keywords are given as singular forms; writing something like INTERVAL 5 MINUTE may seem awkward to you, but it is perfectly correct to MySQL.

关于时间，指定的时间间隔可以是YEAR ， MONTH ， WEEK ， DAY ， HOUR ， MINUTE或SECOND 。 请记住，关键字以单数形式给出； 编写类似INTERVAL 5 MINUTE对您来说似乎很尴尬，但这对MySQL来说是完全正确的。

更新活动 (Updating Events)

If you want to change an existing event’s behavior rather than deleting it and recreating it, you can use ALTER EVENT. For example, to change the schedule of the previous event to run every month, starting at some date in the future at 1 o’clock in the morning, you would use the following:

如果要更改现有事件的行为而不是删除并重新创建它，则可以使用ALTER EVENT 。 例如，要将上一个事件的时间表更改为每个月(从将来的某个日期的早晨1点开始)运行，请使用以下命令：

ALTER EVENT myevent ON SCHEDULE EVERY 1 MONTH STARTS '2011-12-01 01:00:00' |

To update the event with a different set of queries, you would use:

要使用不同的查询集更新事件，您可以使用：

ALTER EVENT myevent DO BEGIN INSERT INTO mystats (total) SELECT COUNT(*) FROM sessions; TRUNCATE sessions; END |

To rename an event, you would specify a RENAME clause:

要重命名事件，您可以指定一个RENAME子句：

ALTER EVENT myevent RENAME TO yourevent;

博客发布计划 (Blog Post Scheduling)

So that I can show you a practical example, let’s say you have a blog and you want the option to schedule posts to be published at some time in the future. One way to achieve this is to add a timestamp and published flag to the database records. A cron script would execute once every minute to check the timestamps and flip the flag for any posts that should be published. But this doesn’t seem very efficient. Another way to achieve this is by using MySQL events that will fire when you want publish the post.

为了让我向您展示一个实际的示例，假设您有一个博客，并且希望可以选择计划在将来的某个时间发布帖子。 一种实现方法是在数据库记录中添加时间戳和已发布标志。 Cron脚本每分钟执行一次，以检查时间戳记并翻转标记以显示应发布的任何帖子。 但这似乎不是很有效。 实现此目的的另一种方法是使用MySQL事件，当您要发布帖子时将触发该事件。

Your blog entry form might have a checkbox that, when checked, indicates this is a scheduled post. Additionally, the form would have input fields for you to enter the date and time of when the post should be published. The receiving script would be responsible for adding the blog entry to the database and managing the events to schedule it if it’s not an immediate post. The relevant code looks like the following:

您的博客条目表单中可能有一个复选框，如果选中该复选框，则表明这是预定的帖子。 此外，该表单将具有输入字段，供您输入发布文章的日期和时间。 如果不是立即发布，则接收脚本将负责将博客条目添加到数据库中，并管理事件以安排它的时间。 相关代码如下所示：

<?php // establish database connection and filter incoming data // ... // insert blog post with pending status, get id assigned to post $query = "INSERT INTO blog_posts (id, title, post_text, status) VALUES (NULL, :title, :postText, 'pending')"; $stm = $db->prepare($query); $stm->execute(array(":title" => $title, ":postText" => $text)); $id = $db->lastInsertId(); // is this a future post? if (isset($_POST["schedule"], $_POST["time"])) { $scheduleDate = strtotime($_POST["time"]); $query = "CREATE EVENT publish_:id ON SCHEDULE AT FROM_UNIXTIME(:scheduleDate) DO BEGIN UPDATE blog_posts SET status = 'published' WHERE id = :id; END"; $stm = $db->prepare($query); $stm->execute(array(":id" => $id, ":scheduleDate" => $scheduleDate)); } // this is not a future post, publish now else { $query = "UPDATE blog_posts SET status = 'published' WHERE id = :id"; $stm = $db->prepare($query); $stm->execute(array(":id" => $id)); }

When the post is stored in the database it is saved with a pending status. This gives you the chance to schedule the event if it’s a scheduled post, otherwise the status can be immediately updated to published.

当帖子存储在数据库中时，它将以待处理状态保存。 如果是预定的帖子，这使您有机会安排事件，否则状态可以立即更新为发布。

If you were to edit the post at a later time, you can delete the event with DROP EVENT IF EXISTS and re-add it with the new scheduled time.

如果您以后要编辑帖子，则可以使用DROP EVENT IF EXISTS删除事件，然后使用新的计划时间重新添加。

摘要 (Summary)

You should now have a solid understanding of what MySQL events are, as well as how to create and manage events of your own. While events are not a replacement for cron jobs or scheduled tasks, as events cannot execute external code such as PHP scripts, they are a useful alternative for time-dependent tasks specific to the MySQL database. As always, if you’re interested in learning more, be sure to read the official documentation.

您现在应该对什么是MySQL事件以及如何创建和管理自己的事件有深刻的了解。 尽管事件不能替代cron作业或计划的任务，但由于事件无法执行外部代码(例如PHP脚本)，因此它们是特定于MySQL数据库的与时间有关的任务的有用替代方法。 与往常一样，如果您想了解更多信息，请务必阅读官方文档 。

Image via Garsya / Shutterstock

图片来自Garsya / Shutterstock

翻译自: https://www.sitepoint.com/working-with-mysql-events/

mysql删除事件的使用