MySQL事件和事件调度程序指南(设置和使用)

MySQL事件和事件调度程序指南介绍MySQL 事件是根据用户定义的计划运行的任务。事件调度器是一个特殊的线程,它在预定的时间执行事件。MySQL 事件很有用,因为它们有助于数据库管理和定期数据库操作任务。在本教程中,你将了解什么是 MySQL 事件以及如何设置和使用它们,包括一些MySQL事件和事件调度使用示例。先决条件:

  • 在数据库服务器上运行 MySQL 的系统
  • 具有 root 权限的 MySQL 用户帐户
什么是 MySQL 事件调度程序?MySQL Event Scheduler 是一个在后台运行并管理调度事件执行的线程。除非全局变量event_scheduler设置为ON或 ,否则调度程序处于睡眠状态1。MySQL Event Scheduler 代表 MySQL对 Cron job的替代。事件调度程序的一些好处是:
  • 它直接写在 MySQL 服务器上。
  • 它独立于平台和应用程序。
  • 只要需要定期更新或清理数据库,就可以使用它。
  • 它减少了查询编译的数量。
注意:在我们的教程中了解有关cron 作业以及如何设置它们的更多信息。MySQL如何使用事件和事件调度?下面我们结合对应的例子一一解析:MySQL 事件调度程序配置MySQL事件和事件调度程序指南:MySQL Event Scheduler 状态可以配置为将 Scheduler 设置为on、off或禁用它。要检查事件调度程序状态,请运行以下命令:
SHOW processlist;

MySQL事件和事件调度程序指南(设置和使用)

文章图片
MySQL事件和事件调度使用示例:event_scheduler结果中显示的系统变量表示事件调度的状态。这里,event_scheduler变量 state 是Waiting on empty queue,这意味着 Scheduler 处于开启状态并等待事件触发它。可能的状态是:
  • ON:事件调度程序线程正在运行并执行所有调度的事件。这是调度程序的默认状态。如果调度程序是ON,则SHOW processlist命令输出将其列为守护进程。
要打开 Event Scheduler  ON,请运行以下命令:
SET GLOBAL event_scheduler = ON;

该值ON可与 互换1
  • OFF:事件调度程序线程没有运行,它没有出现在SHOW processlist.  如果 Event Scheduler 设置为OFF,则不会执行计划的事件。
要打开 Event Scheduler  OFF,请运行以下命令:
SET GLOBAL event_scheduler = OFF;

该值OFF可与 互换0
  • DISABLED:此状态表示事件调度程序无法运行。该线程不会运行,也不会显示在SHOW processlist命令的输出中。
要禁用事件调度程序,请在启动服务器时添加以下命令行选项:
--event-scheduler=DISABLED

注意:事件调度程序只能在服务器启动时禁用,如果其状态设置为ON或,则不能在运行时禁用OFFevent_scheduler如果 Event SchedulerDISABLED在启动时设置为,则无法在运行时更改的值。MySQL 事件调度事件是包含在指定时间或定期执行的 SQL 语句的数据库对象。事件在特定的时间和日期开始和结束。由于 MySQL 事件在用户指定的时间执行,因此这些事件也称为时间  触发器。但是,它们不应与MySQL Triggers混淆,后者是为响应特定事件而执行的数据库对象。因此,在提及计划任务时最好使用术语事件以避免混淆。以下部分讨论如何创建、显示、更改或删除事件。创建新事件
MySQL如何使用事件和事件调度?要创建新事件,请使用以下语法:
CREATE EVENT [ IF NOT EXIST] event_name ON SCHEDULE schedule DO event_body

MySQL事件和事件调度使用示例:该IF NOT EXIST语句确保事件名称对于正在使用的数据库是唯一的。指定唯一的事件名称来代替event_name语法。执行事件的时间表在ON SCHEDULE语句之后指定。事件可以是一次性事件或重复事件。输入 SQL 语句代替关键字event_body后的语法DO。事件主体可以包含存储过程、单个查询或复合语句。在BEGIN END块内编写复合语句。安排事件
MySQL事件和事件调度程序指南:事件可以安排为稍后执行或定期执行。一次性事件一次性事件只执行一次,然后自动删除。要创建一次性事件,请ON SCHEDULE使用以下语法在语句后指定时间戳:
AT timestamp + [ INTERVAL interval]

间隔的可能选择是:
  • YEAR
  • QUARTER
  • MONTH
  • DAY
  • HOUR
  • MINUTE
  • WEEK
  • SECOND
  • YEAR_MONTH
  • DAY_HOUR
  • DAY_MINUTE
  • DAY_SECOND
  • HOUR_MINUTE
  • HOUR_SECOND
  • MINUTE_SECOND
时间戳必须是未来的DATETIME  或TIMESTAMP  值。要指定确切时间,请使用+ INTERVAL、正整数和间隔选项之一向时间戳添加间隔。请注意,这仅适用于使用该CURRENT_TIMESTAMP功能时。例如:
MySQL事件和事件调度程序指南(设置和使用)

文章图片
在这里,事件在创建两天后发生,任务是删除名为test的表。事件在执行后自动删除。如果要将事件保存在数据库中,请ON COMPLETION PRESERVE在创建事件时添加子句。重复事件MySQL事件和事件调度使用示例:重复发生的事件在指定的时间重复发生。要安排重复事件,请在ON SCHEDULE语句后使用以下语法:
EVERY interval STARTS timestamp [ + INTERVAL] ENDS timestamp [ + INTERVAL]

STARTS事件何时开始执行,而关键字指定ENDS关键字指定事件何时停止执行。例如:
MySQL事件和事件调度程序指南(设置和使用)

文章图片
此事件导致 MySQL 每六个月删除一次 table test,立即开始。你还可以指定一个间隔以稍后开始事件。例如:
MySQL事件和事件调度程序指南(设置和使用)

文章图片
你还可以指定事件的开始时间和结束时间:
MySQL事件和事件调度程序指南(设置和使用)

文章图片
此事件使 MySQL从现在开始每六个月删除一次名为test的表,持续五年,从现在开始五天。显示事件
MySQL事件和事件调度程序指南:以下命令显示存储在数据库中的所有事件:
SHOW EVENTS FROM database_name;

请注意,一次性事件在执行后会自动删除,并且不会出现在SHOW EVENTS命令的输出中,除非你ON COMPLETION PRESERVE在创建事件时使用该子句。
MySQL事件和事件调度程序指南(设置和使用)

文章图片
输出列出了存储在指定数据库中的所有事件。更改事件
MySQL如何使用事件和事件调度?使用ALTER EVENT语句更改现有事件:
ALTER EVENT event_name [ ON SCHEDULE schedule ] [ RENAME TO new_event_name ] [ ON COMPLETION [ NOT ] PRESERVE ] [ COMMENT 'comment' ] [ ENABLED | DISABLED ] [ DO sql_statement ]

event_name必须是已存在的事件。后面的所有语句ALTER EVENT都是可选的,具体取决于你要更改的内容。省略ALTER EVENT命令中的任何子句意味着它们保持以前的状态。任何包含的子句都意味着应用你指定的新值。例如:
MySQL事件和事件调度程序指南(设置和使用)

文章图片
在本例中,我们重命名了事件并更改了其 SQL 语句。删除事件(丢弃事件)
MySQL事件和事件调度使用示例:要删除(删除)事件,请使用以下语法:.
DROP EVENT [ IF EXISTS] event_name;

例如:
MySQL事件和事件调度程序指南(设置和使用)

文章图片
此操作会从数据库中永久删除事件。IF EXISTS如果此类事件不存在,则使用该语句会发出警告:
MySQL事件和事件调度程序指南(设置和使用)

文章图片
MySQL事件和事件调度程序指南:MySQL 事件限制使用 MySQL 事件时需要考虑某些限制。他们之中有一些是:
  • 事件不能返回结果集。输出被定向到 dev/null,事件要么失败要么成功,而不通知用户。
  • 事件名称不区分大小写。两个事件不能同名但大小写不同。
  • 事件不能安排在2038 年1 月 19日之后 - Unix 纪元中可以表示的最大值。
  • 另一个存储的程序、触发器或事件不能创建、删除或更改事件。
  • 事件不能创建、删除或更改存储的程序或触发器。
  • 间隔MONTHYEAR_MONTHQUARTERYEAR以月为单位解析。所有其他时间间隔都以秒为单位解决。
  • 可以有两个具有相同调度的事件,但无法强制执行顺序。
  • 事件始终以定义者权限运行。线程以创建事件的用户身份执行事件,并具有该用户的权限。请注意,删除用户不会删除用户创建的事件。
  • 事件不会改变语句的执行次数,这意味着对SHOW STATISTICS命令没有影响。
  • 执行事件的最大延迟为两秒。但是,information_schema.events表始终显示事件执行的准确时间。
  • 使用用户定义的变量,而不是在存储例程内的预准备语句中引用局部变量。
  • 递归调用的次数限制为max_sp_recursion_depth。如果此变量为 0(默认值),则禁用递归。
  • 使用START TRANSACTION语句代替BEGIN WORK, 因为BEGIN WORK被视为BEGIN END块的开始。
  • 存储过程的任何其他限制也适用于事件。
结论MySQL如何使用事件和事件调度?现在你知道如何使用 MySQL Events 来自动化数据库管理。随意尝试不同的语句以查看它们如何影响事件并将它们与其他 MySQL 功能(例如存储过程)结合起来。

    推荐阅读