1. Background

Because the business of the project is constantly moving forward, it is unavoidable that the number of tables in the database will become larger and larger, constantly squeezing hard disk space.Business growth cannot be sustained even with the greatest amount of space, so it is necessary to regularly delete unnecessary data.A table can take up as much space as 4G in our project due to unclear data.Think about how scary it is...

This is about using MySQL to create a timer Event that periodically cleans up previously unnecessary events.

2. Content

#1,Create stored procedures for event calls
delimiter//
drop procedure if exists middle_proce//
create procedure middle_proce()
begin
DELETE 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,open event(To make the timing work, MySQL Constant GlOBAL event_schduleer Must be on Or 1)
show variables like 'event_scheduler'
set global event_scheduler='on'

#3,Establish Evnet Event
drop event if exists middle_event;
create event middle_event
on schedule every 1 DAY STARTS '2017-12-05 00:00:01'
on completion preserve ENABLE
do call middle_proce();

#4,open Event Event
alter event middle_event on completion preserve enable;

#5,Close Event Event
alter event middle_event on completion preserve disable;