MySQL periodically deletes data

Stored procedures:

1. MySQL 5.0 starts supporting stored procedures later.

2. Our commonly used SQL statements need to be compiled and executed at the time of execution, and stored procedures are set of SQL statements to complete a particular function, compiled and stored in the database (and thus executed faster), and executed by the user by making the name of the stored-error procedure and the given parameter.

Event Events:

1. MySQL 5.1 began to introduce the concept of the event;

2. An event is a "time trigger" that triggers an SQL statement or a stored procedure execution at a specific time.

MySQL Timing Delete Data implementation method : MySQL stored procedure + Event events

1. Write Stored procedures

DELIMITER//

CREATE PROCEDURE Del_data (in ' date_inter ' int)//Creating a stored procedure, in indicates that a parameter needs to be passed in

Begin

Delete from Episode_ccm_general_info where time < Date_sub (Curdate (), Interval date_inter day);

Delete from Episode_cem_general_info where time < Date_sub (Curdate (), Interval date_inter day);

End//

DELIMITER;

Format Note:

A. Here's delimiter//:

delimiter is the keyword, the function of this sentence is to declare the delimiter AS//, because MySQL defaults to ";" As a delimiter, if the new delimiter is not declared, the compiler processes the stored procedure as an SQL statement, and the stored procedure compiles with an error. So declaring the current segment's delimiter in advance is '//' and the stored procedure does not execute the code. DELIMITER; is to restore the delimiter.

B. In input parameters, out output parameters, inout for input and output parameters

C. The beginning and end of the stored procedure body is identified by begin,end;

D. Curdate () returns the current date, e.g. 2015-06-18

Curtime () returns the current time, e.g. 14:12:09

Now () returns the current date and time, e.g. 2015-06-18 14:12:09

Date_sub () function, minus the specified time interval from the date;

E. View stored Procedure command: Show procedure status where db = "dbname";

F. Delete a stored procedure command: drop procedure;

The G. Delete statement represents the deletion of data date_inter days ago.

2. Write Event Events

Create Event Del_event

On schedule

Every 1 day

Starts ' 2015-05-11 13:00:00 '

On completion PRESERVE ENABLE

Do call Del_data (90)

A. Check to see if the event is turned on:

Show variables like "Event_scheduler";

B. Open event: (1:on 0:off)

Set global Event_scheduler = 1;

C. On schedule a scheduled task in two ways:

i). At time stamp, used to complete a single time scheduled task;

II). Every time, used to complete repetitive scheduled tasks; time can be used: year, month, day, hour, minute,seconds, etc.

D. On completion [NOT] Preserve

On completion means that when the event does not occur again, preserve indicates that the event will not be dropped after it has been executed.

Enable/disable indicates an on/off event.

You can change the opening or closing of an event via alter event Event_Name Enable/disable;

E. Call Del_data is called a stored procedure;

F. Show Events view event;

MySQL periodically deletes data