Detailed explanation of how to create MySql scheduled tasks in navicat

Detailed explanation of creating MySql scheduled tasks with navicat

An event is a procedural database object that MySQL calls at a specific time. An event can be called once or started periodically. It is managed by a specific thread, the so-called "event scheduler". Events are similar to triggers in that they are triggered when something happens. A trigger is fired when a statement is started on the database, whereas an event is fired based on a scheduled event. Because of their similarity to each other, events are also called temporary triggers. Events replace the work that could previously only be performed by the operating system's scheduled tasks, and MySQL's event scheduler can accurately execute one task per second, while the operating system's scheduled tasks (such as CRON under Linux or task scheduling under Windows) can only be executed once a minute.

1. Start a scheduled task

1. Scheduled tasks are closed by default, and when the Value is ON, they are turned on;

SHOW VARIABLES LIKE '%event_sche%';

2. Start a scheduled task

SET GLOBAL event_scheduler = 1;

3. Turn off scheduled tasks

SET GLOBAL event_scheduler = 0;

If you need to run a scheduled task for a long time, you need to configure event_scheduler = on in my.ini

2. Create a scheduled task through navicat

1. Find事件and create a new event

2. The definition is to fill in the SQL or event or stored procedure that needs to be run.
3. The plan is the operating rules of this scheduled task.
There are two types of events in the plan, AT and EVERY. EVERY is also called the "event scheduler". An event can be called once or started periodically. It is managed by a specific thread.

The setting here is to execute once every day starting from 2020-08-06 12:00:00.
Click Preview SQL to view the creation of scheduled tasks.

4. Here are many examples for you to understand this setting.

1. Execute an update CREATE EVENT myevent one hour after the event myevent is created
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
 UPDATE myschema.mytable SET mycol = mycol + 1;

2. Clear the test table at 12:00 on March 20, 2014:

CREATE EVENT e_test
  ON SCHEDULE AT TIMESTAMP '2014-03-20 12:00:00'
  DO TRUNCATE TABLE test.aaa;

3.5 days later, start clearing the test table every day:

CREATE EVENT e_test
  ON SCHEDULE EVERY 1 DAY
  STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
  DO TRUNCATE TABLE test.aaa;

4. Clear the test table regularly every day and stop executing CREATE EVENT e_test after 5 days
  ON SCHEDULE EVERY 1 DAY
  ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
  DO TRUNCATE TABLE test.aaa;

5. After 5 days, start clearing the test table every day, and stop after one month:

CREATE EVENT e_test
  ON SCHEDULE EVERY 1 DAY
  STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
  ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
  DO TRUNCATE TABLE test.aaa;

6. Clear the test table regularly every day (only execute once, and terminate the event after the task is completed):

CREATE EVENT e_test
  ON SCHEDULE EVERY 1 DAY
  ON COMPLETION NOT PRESERVE
  DO TRUNCATE TABLE test.aaa;

[ON COMPLETION [NOT] PRESERVE] can set this event to be executed once or persistently. The default is NOT PRESERVE.

3. Close, start, alias, move, delete event

Modify the scheduled task ALTER. The only difference between modifying and creating is that the first word of creation is CREATE, which is changed to ALTER.

ALTER EVENT myevent 
... Specific changes

Temporarily close an event

ALTER EVENT myevent DISABLE;

Start an event

ALTER EVENT myevent ENABLE;

Alias ​​an event

ALTER EVENT olddb.myevent
RENAME TO newdb.myevent;

Move myevent from olddb to newdb

ALTER EVENT olddb.myevent
RENAME TO newdb.myevent;

Deleting an event

DROP EVENT [IF EXISTS] event_name

4. Query Event Information

Event information related table

information_schema.events
mysql.event

View the creation information of the event

show create event countsum \G

View the events information of the sem library

USE sem;
SHOW EVENTS \G

This is the end of this article about the detailed explanation of how to create MySql scheduled tasks with Navicat. For more information about how to create MySql scheduled tasks with Navicat, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!