Movatterモバイル変換

ALTER    [DEFINER =user]    EVENTevent_name    [ON SCHEDULEschedule]    [ON COMPLETION [NOT] PRESERVE]    [RENAME TOnew_event_name]    [ENABLE | DISABLE | DISABLE ON SLAVE]    [COMMENT 'string']    [DOevent_body]

TheALTER 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 theDEFINER,ON SCHEDULE,ON COMPLETION,COMMENT,ENABLE /DISABLE, andDO clauses is exactly the same as when used withCREATE EVENT. (SeeSection 15.1.13, “CREATE EVENT Statement”.)

Any user can alter an event defined on a database for which that user has theEVENT privilege. When a user executes a successfulALTER EVENT statement, that user becomes the definer for the affected event.

ALTER EVENT works only with an existing 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 namedmyevent is defined as shown here:

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 formyevent from once every six hours starting immediately to once every twelve hours, starting four hours from the time the statement is run:

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 bymyevent to one that deletes all records frommytable; it also changes the schedule for the event such that it executes once, one day after thisALTER EVENT statement is run.

ALTER EVENT myevent    ON SCHEDULE      AT CURRENT_TIMESTAMP + INTERVAL 1 DAY    DO      TRUNCATE TABLE myschema.mytable;

Specify the options in anALTER EVENT statement only for those characteristics that you want to change; omitted options keep their existing values. This includes any default values forCREATE EVENT such asENABLE.

To disablemyevent, use thisALTER EVENT statement:

ALTER EVENT myevent    DISABLE;

TheON SCHEDULE clause may use expressions involving built-in MySQL functions and user variables to obtain any of thetimestamp orinterval 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 useSELECT FROM DUAL. This is true for bothALTER EVENT andCREATE 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).

Although anALTER EVENT statement that contains anotherALTER EVENT statement in itsDO clause appears to succeed, when the server attempts to execute the resulting scheduled event, the execution fails with an error.

To rename an event, use theALTER EVENT statement'sRENAME TO clause. This statement renames the eventmyevent toyourevent:

ALTER EVENT myevent    RENAME TO yourevent;

You can also move an event to a different database usingALTER EVENT ... RENAME TO ... anddb_name.event_name notation, as shown here:

ALTER EVENT olddb.myevent    RENAME TO newdb.myevent;

To execute the previous statement, the user executing it must have theEVENT privilege on both theolddb andnewdb databases.

The valueDISABLE ON SLAVE is used on a replica instead ofENABLE orDISABLE 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. SeeSection 19.5.1.16, “Replication of Invoked Features”, for more information.