Mysql timer and mysql scheduled task

Source: Internet
Author: User

Mysql timer and mysql scheduled task

Mysql timer is a mysql event. In actual development, we sometimes need to perform some operations on a regular basis. Most people create a timer using the ScheduledExecutorService class. In this case, when big data is updated, the running speed is very busy. At this time, we can consider using the mysql timer to execute the SQL script file.

Use the mysql timer.

First, you must start the scheduler "event_scheduler ".

There are three ways to check whether an event plan (Scheduler) Is Enabled:

1) show variables like 'event _ schedting ';

2) SELECT @ event_scheduler;

3) show processlist;

There are four ways to enable the event Scheduler:

1) set global event_scheduler = 1;

2) SET @ global. event_scheduler = 1;

3) set global event_scheduler = ON;

4) SET @ global. event_scheduler = ON;

The second step is to create a timer event, which can be created through SQL or Navicat Premium (mysql client) installation.

1) create a timer event using SQL:

Create event if not existseventJob
On schedule every 2 second STARTS '2017-07-14 00:00:00'
On completion PRESERVE

ENABLE
Do callmypro ();

In the preceding SQL script, "eventJob" is the timer event name, and "mypro" is the mysql function or stored procedure. The SQL script indicates that the mypro () function is executed every two seconds starting from '2017-07-14 00:00:00. You can also set the end time.

2) create a timer event on the client:

As shown in, the call keyword is followed by the function or stored procedure to be executed. The status indicates the status of the timer event, whether to enable, enable indicates enabled, and disable indicates disabled, you can set it manually or through SQL. on completion is set to "PRESERVE ".

In the "Schedule" label, the timer frequency and start and end time are mainly set. AT indicates that the execution starts from the current mysql database time (starting from the start time of the starter, that is, when the status is "ENABLE") AT a certain frequency; EVERY is the custom start time and end time. It is more flexible and easy to use than EVERY.

Finally, you can set the status of the timer event.

In the previous step, you can set the status for both SQL creation and client creation. However, when we do not set the status or disable the timer event, you need to execute the SQL

Alter event eventJob on completion preserve enable; ---- ENABLE EVENT
ALTER EVENTeventJob on completion preserve disable; ---- close the event

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.

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.