ALTER
[DEFINER = user]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'string']
[DO event_body]
The ALTER EVENT statement changes one or more of the characteristics of an existing event without the need to drop and recreate it. The syntax for each of the DEFINER
, ON SCHEDULE
, ON COMPLETION
, COMMENT
, ENABLE
/ DISABLE
, and DO clauses is exactly the same as when used with CREATE EVENT. (See Section 13.1.13, “CREATE EVENT Statement”.)
ALTER EVENT语句更改现有事件的一个或多个特征,而不需要删除和重新创建它。每个DEFINER、ON SCHEDULE、ON COMPLETION、COMMENT、ENABLE / DISABLE和DO子句的语法与与CREATE EVENT一起使用时完全相同。(参见13.1.13节“CREATE EVENT Statement”)
Any user can alter an event defined on a database for which that user has the EVENT privilege. When a user executes a successful ALTER EVENT statement, that user becomes the definer for the affected event.
任何用户都可以修改其拥有event特权的数据库上定义的事件。当用户成功执行ALTER EVENT语句时,该用户将成为受影响事件的定义者。
ALTER EVENT works only with an existing event:
ALTER EVENT仅适用于已存在的事件:
mysql> ALTER EVENT no_such_event
> ON SCHEDULE
> EVERY '2:3' DAY_HOUR;
ERROR 1517 (HY000): Unknown event 'no_such_event'
In each of the following examples, assume that the event named myevent
is defined as shown here:
在下面的例子中,假设名为myevent的事件定义如下所示:
CREATE EVENT myevent
ON SCHEDULE
EVERY 6 HOUR
COMMENT 'A sample comment.'
DO
UPDATE myschema.mytable SET mycol = mycol + 1;
The following statement changes the schedule for myevent
from once every six hours starting immediately to once every twelve hours, starting four hours from the time the statement is run:
下面的语句将myyevent的时间表从立即开始的每6小时一次改为每12小时一次,从语句运行的4小时开始:
ALTER EVENT myevent
ON SCHEDULE
EVERY 12 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 4 HOUR;
It is possible to change multiple characteristics of an event in a single statement. This example changes the SQL statement executed by myevent
to one that deletes all records from mytable
; it also changes the schedule for the event such that it executes once, one day after this ALTER EVENT statement is run.
可以在单个语句中更改事件的多个特征。这个例子将myevent执行的SQL语句更改为删除mytable中所有记录的语句;它还会更改事件的调度,使其在运行ALTER event语句的一天后执行一次。
ALTER EVENT myevent
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
TRUNCATE TABLE myschema.mytable;
Specify the options in an ALTER EVENT statement only for those characteristics that you want to change; omitted options keep their existing values. This includes any default values for CREATE EVENT such as ENABLE
.
在ALTER EVENT语句中仅为您想要更改的特征指定选项;省略的选项保留它们现有的值。这包括CREATE EVENT的任何默认值,如ENABLE。
To disable myevent
, use this ALTER EVENT statement:
要禁用myevent,请使用ALTER EVENT语句:
ALTER EVENT myevent
DISABLE;
The ON SCHEDULE
clause may use expressions involving built-in MySQL functions and user variables to obtain any of the timestamp
or interval
values which it contains. You cannot use stored routines or loadable functions in such expressions, and you cannot use any table references; however, you can use SELECT FROM DUAL
. This is true for both ALTER EVENT and CREATE EVENT statements. References to stored routines, loadable functions, and tables in such cases are specifically not permitted, and fail with an error (see Bug #22830).
ON SCHEDULE子句可以使用包含内置MySQL函数和用户变量的表达式来获取它所包含的时间戳或时间间隔值。不能在这样的表达式中使用存储的例程或可加载的函数,也不能使用任何表引用;但是,您可以使用SELECT FROM DUAL。对于ALTER EVENT和CREATE EVENT语句都是这样。在这种情况下,对存储例程、可加载函数和表的引用是不允许的,并且会以错误的形式失败(参见Bug #22830)。
Although an ALTER EVENT statement that contains another ALTER EVENT statement in its DO clause appears to succeed, when the server attempts to execute the resulting scheduled event, the execution fails with an error.
尽管在DO子句中包含另一个ALTER EVENT语句的ALTER EVENT语句似乎成功,但当服务器试图执行生成的计划事件时,执行失败并出现错误。
To rename an event, use the ALTER EVENT statement's RENAME TO
clause. This statement renames the event myevent
to yourevent
:
要重命名事件,请使用ALTER event语句的rename To子句。该声明将事件myevent重命名为yourevent:
ALTER EVENT myevent
RENAME TO yourevent;
You can also move an event to a different database using ALTER EVENT ... RENAME TO ...
and
notation, as shown here:db_name.event_name
您还可以使用ALTER event…重命名…db_name。Event_name表示法,如下所示:
ALTER EVENT olddb.myevent
RENAME TO newdb.myevent;
To execute the previous statement, the user executing it must have the EVENT privilege on both the olddb
and newdb
databases.
要执行前面的语句,执行它的用户必须在olddb和newdb数据库上都具有EVENT特权。
Note
There is no RENAME EVENT
statement.
The value DISABLE ON SLAVE
is used on a replica instead of ENABLE
or DISABLE
to indicate an event that was created on the replication source server and replicated to the replica, but that is not executed on the replica. Normally, DISABLE ON SLAVE
is set automatically as required; however, there are some circumstances under which you may want or need to change it manually. See Section 17.5.1.16, “Replication of Invoked Features”, for more information.
值DISABLE ON SLAVE用于副本,而不是ENABLE或DISABLE,表示在复制源服务器上创建并复制到副本,但没有在副本上执行的事件。正常情况下,DISABLE ON SLAVE会根据需要自动设置;然而,在某些情况下,您可能希望或需要手动更改它。