MySQL Scheduled tasks are scheduled to execute daily

Source: Internet
Author: User

code example:
1 ' 2012-07-18 00:00:00 ' On completion preserveenabledoupdate ' om_courselist ' SET ' status '=6 WHERE ' status ' =5 and ' Closetime ' <current_timestamp ();

You can create events with Navicat, and more quickly:

Navicat creating and designing MySQL Events

1. Turn on the timer 0:off 1:on

SET GLOBAL event_scheduler = 1;

2. Select a database on the left side of Navicat, and click "Time"-"create event" to bring up a window.

In the Define option, enter the SQL statement, beginning with begin, ending with end.

If you are running locally, definer is [email protected].

3. Schedule tasks that require operations at regular intervals, such as creating tables, deleting data, and so on.

3-1. At timestamp, used to complete a single scheduled task.

3-2. Every time (units) The number of time units [starts timestamp] [ends timestamp], which is used to complete a recurring scheduled task. More time units, on-demand selection.

In both scheduled tasks, the timestamp can be any of the timestamp and datetime data types, and the timestamp needs to be greater than the current time.

Last save is OK.

The syntax for the MySQL event is attached:

MySQL or MariaDB event syntax

MySQL event plan is added in MySQL 5.1.6. The MySQL event is to run the work as planned. Therefore, we sometimes call them planned events. When you create an event, you create a well-known database object that consists of running one or more SQL statements in one or more regular intervals, starting and ending at a specific date time. Conceptually, this is a concept similar to the Windows Task Scheduler. Click to open the object list for the event .

Defined

The definition includes a valid SQL statement. This can be a simple statement, such as SELECT or INSERT, or it can be a compound statement written with BEGIN and END . Compound statements can contain declaration loops and other control structure statements.

tip: to customize the view of the editor and see more SQL editing features, see Editor advanced features.

Defined by
Specifies the user account to be used to check access permissions when the event runs. The default definition value is the user who runs the CREATE EVENT statement. (This is the same as Definer = Current_User.) If given a user value, it should be a ' user_name ' @ ' host_name ' format of the MySQL account (using the same format as in the GRANT statement). Both the user_name and HOST_NAME values are required.

State
You can create an event but you can keep it inactive with the DISABLE keyword. Alternatively, you can use enable to clear the default state, which is active.

On completion
Normally, once an event has expired, it is immediately deleted. You can override this performance by specifying the on completion PRESERVE . Using on completion not PRESERVE only makes the default non-persistent performance explicit.

Plan

At
at timestamp is used for one-time events. It specifies that the event runs only once for the timestamp date and time, that it must contain both a date and time, or that it must be an expression that resolves to a datetime value. Use +interval to create an event that occurs sometime in the future related to the current date and time.

Every
Repeat the action at regular intervals, you can use a every clause followed by a interval. (+interval and every cannot be used at the same time.) )

Starts
The every clause may also contain a non-mandatory starts clause. starts is followed by a timestamp value that indicates when the action should start repeating, or you can use the +interval interval to specify the amount of time to start from now.

For example:every 3 month starts Current_timestamp + 1 WEEK means "every 3 months, starting from now one week later".

ENDS
The every clause can also contain a non-mandatory ENDS clause. The ENDS keyword is followed by a timestamp value that tells MySQL when to stop repeating events. You can also use the +interval interval with ENDS .

For example:every HOUR starts Current_timestamp + INTERVAL, MINUTE ENDS current_timestamp + INTERVAL 4 WEEK equivalent to "every 12 Hours, starting 30 minutes from now and ending in four weeks from now. "

P.S. timestamp must be in the future-you can't schedule an event to happen in the past.

The interval interval consists of two parts, quantity and time unit *.
*year | QUARTER | MONTH | Day | HOUR | MINUTE |
WEEK | SECOND | Year_month | Day_hour | Day_minute |
Day_second | Hour_minute | Hour_second | Minute_second

Source: http://www.navicat.com.cn/manual/online_manual/cn/navicat/win_manual/EventsMySQL.html

MySQL Scheduled tasks are scheduled to execute daily

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.