MySQL database Event scheduled execution task, mysqlevent

MySQL database Event scheduled execution task, mysqlevent
I. background

As the business of the Project is constantly moving forward, it is inevitable that the number of tables in the database will become larger and larger, and the hard disk space will be occupied. Even a large amount of space can not support business growth, so it is necessary to regularly Delete unnecessary data. In our project, because we do not clean up data, a table actually occupies 4 GB of space. Think about how terrible it is...

This section describes how to use MySQL to create a timer Event to regularly clear unnecessary events.

Ii. Content

#1. create a stored procedure for the event to call delimiter // drop procedure if exists middle_proce // create procedure middle_proce () beginDELETE FROM jg_bj_comit_log WHERE comit_time <SUBDATE (NOW (), INTERVAL 2 MONTH); delete from jg_bj_order_create WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH); delete from jg_bj_order_match WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH ); delete from jg_bj_order_cancel WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH); delete from jg_bj_operate_arrive WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH ); delete from jg_bj_operate_depart WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH); delete from jg_bj_operate_login WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH ); delete from jg_bj_operate_logout WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH); delete from jg_bj_operate_pay WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH ); delete from jg_bj_position_driver WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH); delete from jg_bj_position_vehicle WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH ); delete from jg_bj_rated_passenger WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH); end // delimiter; #2. Enable event (to enable timing, mySQL's constant GlOBAL event_schduleer must be on or 1) show variables like 'event _ scheduler 'set global event_schedables = 'on' #3. Create an Evnet event drop event if exists middle_event; create event middle_eventon schedule every 1 day starts '2017-12-05 00:00:01 'on completion preserve ENABLEdo call middle_proce (); #4. enable Event event alter event middle_event on completion preserve enable; #5. disable Event event alter Event middle_event on completion preserve disable;