How to use stored procedure + events in MySQL

I. BACKGROUND

The interface operation log is stored in the Operationlog table in the MySQL database, and if the table cannot be backed up automatically, the data in the table becomes more and more, affecting the speed. You can periodically back up the data in a table to another table to resolve.

Second, the solution

1. Use the stored procedure + event in MySQL to resolve.

The stored procedure logic is:

1) Create a new table Operationlog_temp, the fields are the same as Operationlog;

2) Rename the table Operationlog to OPERATIONLOG_YYYY-MM-DD;

3) Rename the table Operationlog_temp to Operationlog

The event logic is:

1) Periodically call the stored procedure every 3 months Bakoplog

2. Define the stored procedure bakoplog:

Note
The Start Event function (MySQL must first turn on the event function to use the event), with the following SQL statement
SHOW VARIABLES like ' Event_scheduler ';
SET GLOBAL event_scheduler = on;

CREATE definer= ' sa ' @ '% ' PROCEDURE ' Bakoplog ' () begincreate table operationlog_temp like Operationlog;set @i=current_ Date ();--Execute Rename table Operationlog to Operationlog_yyyy-mm-ddset @sqlstr =concat (' Rename table Operationlog to ') Operationlog_ ', cast (@i as Char), '); Select @sqlstr; PREPARE Renameoplog from @sqlstr; EXECUTE renameoplog;rename table Operationlog_temp to Operationlog; END;

3. Define Event Callprocedurebakoplog

CREATE definer= ' sa ' @ '% ' EVENT ' callprocedurebakoplog ' on SCHEDULE every 1 day starts ' 2014-12-30 00:00:00 ' ENDS ' 2015-01-0 6 00:00:00 ' On completion PRESERVE ENABLE does call Bakoplog ();

4. Some syntax used by the stored procedure

Set @i=current_date ();//assigns the global variable I to the current date set @sqlstr =concat (' Rename table Operationlog to ' Operationlog_ ', cast (@i as Char) //sqlstr=rename table Operationlog to Operationlog_yyyy-mm-ddprepare renameoplog from @sqlstr;//define pre-processing statement execute renameoplog;//execute pre-processing statements to view the event created show events; can also be seen in the MySQL Library event table 1) temporary Shutdown event alter event E_test DISABLE;2) Open event alter event E_ Test enable;3) Change the daily empty test table to 5 days to empty: ALTER event E_teston SCHEDULE every 5 day;4) Delete event drop events [IF EXISTS] Even T_name For example, remove the E_test event that was created earlier in the drop events e_test; If this event exists, it will generate error 1513 (HY000): Unknown event error, so it is best to add if Existsdrop EVENT IF EXISTS e_test;

5. Using the MySQL management tool mysql-front operation stored procedures, Events introduction operation

1) Define stored procedures, events

　　

2) View the event table directly in the MySQL library, with the events defined in the table

How to use stored procedure + events in MySQL