Mysql storage program and Timer

Practical mysql storage program and timer requirements: A large log table is used to regularly count the total number of days each day and put it in another table for easy viewing and operation .? Old solution: Write the program in Script Mode and run it regularly with linux crontab .? This article focuses on using the mysql timer to regularly execute the storage program .? Step 1: Write a storage Program

Practical mysql storage program and timer requirements: A large log table, which is used to regularly count the total number of days each day and put it in another table for easy viewing and operation. ? Old solution: Write the program in Script Mode and execute it regularly using linux crontab. ? This article focuses on using the mysql timer to regularly execute the storage program. ? Step 1: Write a storage Program

Mysql storage program and Timer

Requirement: a huge log table is used to calculate the total number of days on a regular basis every day and put it in another table for easy viewing and operation.

?

Old solution: Write the program in Script Mode and execute it regularly using linux crontab.

?

This article focuses on using the mysql timer to regularly execute the storage program.

?

Step 1: Write a storage Program (you need to understand the syntax of the basic storage Program)

?

create procedure inproc()begindeclare done int default 0;declare a,b,c int;declare curl cursor for select ver,date_format(time,'%Y%m%d') as dt,count(*) as count from ty.count where time>date_sub(curdate(),interval 1 day) group by ver,dt;declare continue handler for sqlstate '02000' set done = 1;open curl;repeatfetch curl into a,b,c;if not done theninsert into ty.daycount values (null,b,a,c);end if;until done end repeat;close curl;end

This storage program uses declare to define local variables, declare the curl cursor, and use the cursor until it traverses the result set.

?

?

Before executing this statement, you must first

delimiter $$

After the execution is complete

?

?

delimiter ;

?

Use show to check whether the operation is successful.

?

show procedure status like '%%';

?

?

Step 2: Enable the mysql Timer

If it is not on, run

?

set global event_scheduler=1;

Mysql does not need to be restarted

?

Mysql has multiple daemon processes.

(Note :? For our online environment, when using event, note that the timer is enabled on the master database, and the timer is disabled from the slave database. If event triggers all operations, binlog is recorded for master-slave synchronization, enabling the timer on the slave database may cause the card library. After switching the master database, remember to open the timer on the new master database .)

?

Step 3: Create a scheduled task

Syntax:

The create event syntax is as follows:

CREATE EVENT

[If not exists] ------------------------------------------------- * mark 1

Event_name ------------------------------------------------------- * mark 2

?

On schedule schedule ------------------------------------ * mark 3?

[On completion [NOT] PRESERVE] ----------------- * mark 4

[ENABLE | DISABLE] ------------------------------------------ * mark 5?

[COMMENT 'comment'] ------------------------------------------ * mark 6?

DO SQL _statement --------------------------------------------------- * mark 7

;

?

NOTE 3: ON SCHEDULE

?

You can SCHEDULE a task on schedule in either of the following ways:

?

1. AT timestamp, used to complete a single scheduled task.

?

2. The number and time unit of EVERY time (unit) [STARTS timestamp] [ENDS timestamp] to complete repeated scheduled tasks.

?

In two scheduled tasks, the TIMESTAMP can be of any TIMESTAMP or DATETIME data type. The TIMESTAMP must be greater than the current time.

?

In a repeated scheduled task, the number of time (unit) can be an integer of any non-Null value. The time unit is key words: YEAR, MONTH, DAY, HOUR, MINUTE or SECOND.

?

Note: Other time units are also valid, such as QUARTER, WEEK, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, and MINUTE_SECOND. We do not recommend using these nonstandard time units.

?

Note 4: [on completion [NOT] PRESERVE]

?

The on completion parameter indicates "when this event does not occur again", that is, when a single scheduled task is completed or when a repetitive scheduled task is executed to the ENDS stage. PRESERVE is used to prevent the EVENT from being dropped after execution. We recommend that you use this parameter to view the EVENT details.

?

?

?

Note 5: [ENABLE | DISABLE]

The Enable and Disable parameters indicate setting the event status. Enable indicates that the system will execute this event. Disable indicates that the system does not execute this event.

?

You can use the following command to close or enable the event:

ALTER EVENT event_name  ENABLE/DISABLE

?

The following is my instance, which is executed every morning.

CREATE EVENT `event_call_inproc` ON SCHEDULE EVERY 1 DAY STARTS '2013-09-12 01:00:00' ON COMPLETION PRESERVE ENABLE DO begin call ty.inproc();end

?

Other examples:

Execute create event event_call_defer on schedule every 1 day starts date_add (date (curdate () + 1), interval 3 hour) on completion preserve enabledobegin call test every three o'clock. warn (); end: Execute create event EVENT2 on schedule every 1 month starts DATE_ADD (DATE_SUB (CURDATE (), interval day (CURDATE () at each MONTH ()) -1 DAY), INTERVAL 1 MONTH), INTERVAL 1 HOUR) on completion preserve enabledo begin call stat (); END execute create event TOTAL_SEASON_EVENTON schedule every 1 quarter starts DATE_ADD (DATE (CONCAT (YEAR (CURDATE ()),'-', ELT (QUARTER (CURDATE (),), '-', 1), INTERVAL 1 QUARTER), INTERVAL 2 HOUR) on completion preserve enabledo begin call SEASON_STAT (); END execute create event TOTAL_YEAR_EVENTON schedule every 1 year starts DATE_ADD (DATE (CONCAT (YEAR (CURDATE () + 1, '-', 1, '-', 1), INTERVAL 4 HOUR) on completion preserve enabledo begin call YEAR_STAT (); END

?

(This article focuses on practice, grammar, and other knowledge. Please refer to the relevant manual)