MySQL database Event timed to perform tasks

I. BACKGROUND

As the business of the project is constantly running forward, it is inevitable that the amount of database tables will become more and more large, constantly crowding out hard disk space. It is necessary to delete unnecessary data periodically, even if large spaces cannot support the growth of the business. In our project, due to not clean up the data, a table accounted for more than 4G of space. Think how horrible it is ...

Here is the use of MySQL to establish a timer event, regular removal of the previous unnecessary events.

Second, the content

#1, establishing a stored procedure for event invocation delimiter//Drop procedure if existsMiddle_proce//Create procedureMiddle_proce ()beginDELETE  fromJg_bj_comit_logWHEREComit_time<Subdate (now (), INTERVAL2 MONTH);DELETE  fromJg_bj_order_createWHEREcreated_on<Subdate (now (), INTERVAL3 MONTH);DELETE  fromJg_bj_order_matchWHEREcreated_on<Subdate (now (), INTERVAL3 MONTH);DELETE  fromJg_bj_order_cancelWHEREcreated_on<Subdate (now (), INTERVAL3 MONTH);DELETE  fromJg_bj_operate_arriveWHEREcreated_on<Subdate (now (), INTERVAL3 MONTH);DELETE  fromJg_bj_operate_departWHEREcreated_on<Subdate (now (), INTERVAL3 MONTH);DELETE  fromJg_bj_operate_loginWHEREcreated_on<Subdate (now (), INTERVAL3 MONTH);DELETE  fromJg_bj_operate_logoutWHEREcreated_on<Subdate (now (), INTERVAL3 MONTH);DELETE  fromJg_bj_operate_payWHEREcreated_on<Subdate (now (), INTERVAL3 MONTH);DELETE  fromJg_bj_position_driverWHEREcreated_on<Subdate (now (), INTERVAL3 MONTH);DELETE  fromJg_bj_position_vehicleWHEREcreated_on<Subdate (now (), INTERVAL3 MONTH);DELETE  fromJg_bj_rated_passengerWHEREcreated_on<Subdate (now (), INTERVAL3 MONTH);End//delimiter;#2, turn on event (to make timing work, MySQL's constant global event_schduleer must be on or 1) show variables like 'Event_scheduler'SetGlobal Event_scheduler=' on'#3, creating evnet eventsDropEventif existsmiddle_event;CreateEvent Middle_event onSchedule every1  DayStarts'2017-12-05 00:00:01' onCompletion Preserve Enabledo call Middle_proce ();4, turn on event eventsAlterEvent Middle_event onCompletion Preserve enable;#5, Close event eventsAlterEvent Middle_event onCompletion preserve disable;

MySQL database Event timed to perform tasks