Usage of mysqlevent

Welcome to the Linux community forum and interact with 2 million technical staff to access the event function supported by MySQL from 5.1, similar to the oracle job function. With this function, we can enable MySQL to automatically execute data aggregation and other functions without the need for timer support provided by the operating system. For example, linuxcrontab. Create test table

Welcome to the Linux community forum and interact with 2 million technicians> go to MySQL and start from 5.1 to support the event function, similar to the oracle job function. With this function, we can enable MySQL to automatically execute data aggregation and other functions without the need for timer support provided by the operating system. For example, the linux crontab function. Create test table

Welcome to the Linux community forum and interact with 2 million technicians>

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 need for timer support provided by the operating system. 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