MySQL timer EVENT Learning

MySQL supports the event function and oracle-like job function from 5.1. With this function, we can enable MySQL to automatically execute data aggregation and other functions without the support of operations previously required. For example, linuxcrontab. Create test table CREATETABLEt (vVARCHAR (100) NOTNULL) ENGINEINNODBDEFAULTCHARSET

MySQL supports the event function and oracle-like job function from 5.1. With this function, we can enable MySQL to automatically execute data aggregation and other functions without the support of operations previously required. For example, the linux crontab function. Create table t (v VARCHAR (100) not null) ENGINE INNODB DEFAULT CHARSET

MySQL supports the event function from 5.1, similar to the job function of oracle. With this function, we can enable MySQL to automatically execute data aggregation and other functions without the support of operations previously required. For example, the linux crontab function.

Create test table

Create table t

(

V VARCHAR (100) NOT NULL

) Engine innodb default charset = utf8;

Create a stored procedure for timer calls

DELIMITER $

Drop procedure if exists e_test $

Create procedure e_test ()

BEGIN

Insert into t VALUES ('1 ');

END $

DELIMITER;

The constant GLOBAL event_scheduler of MySQL must be on or 1.

-- Check whether the timer is enabled

Show variables like '% sche % ';

-- Enable the timer 0: off 1: on

Set global event_scheduler = 1;

-- Create an event

-- Automatically calls the e_test () stored procedure every second

Create event if not exists event_test

On schedule every 1 SECOND

ON COMPLETION PRESERVE

Do call e_test ();

-- Enable event

Alter event event_test ON

Completion preserve enable;

-- Close the event

Alter event event_test ON

Completion preserve disable;

SELECT * FROM t;

Some examples:

Scheduled execution every nine days from now on

Create event EVENT1

On schedule every 9 day starts now ()

ON COMPLETION PRESERVE ENABLE

DO

BEGIN

Call total ();

END

Execute the task at on the first day of each month.

Create event EVENT2

On schedule every 1 month starts DATE_ADD (DATE_SUB (CURDATE (), interval day (CURDATE ()-1 DAY), INTERVAL 1 MONTH), INTERVAL 1 HOUR)

ON COMPLETION PRESERVE ENABLE

DO

BEGIN

Call stat ();

END

Run at on Monday 1.

Create event TOTAL_SEASON_EVENT

On schedule every 1 quarter starts DATE_ADD (DATE (CONCAT (YEAR (CURDATE (), '-', ELT (QUARTER (CURDATE (), 1, 4, 7, 10 ), '-', 1), INTERVAL 1 QUARTER), INTERVAL 2 HOUR)

ON COMPLETION PRESERVE ENABLE

DO

BEGIN

CALL SEASON_STAT ();

END

Execute at on January 1, January 1 every year

Create event TOTAL_YEAR_EVENT

On schedule every 1 year starts DATE_ADD (DATE (CONCAT (YEAR (CURDATE () + 1, '-', 1, '-', 1), INTERVAL 4 HOUR)

ON COMPLETION PRESERVE ENABLE

DO

BEGIN

CALL YEAR_STAT ();

END

The MySQL event and Oralce Job are still a little different, which makes me more troubled.

During automatic calling of the stored procedure on a monthly, quarterly, or yearly basis, you can change the system to the last day of the year for testing, for example, 23:59:55;

This Oracle Job will execute the monthly, quarterly, and yearly stored procedures. However, when MySQL changes the system time, the Event is not scheduled.