MySQL database Event scheduled execution task details, mysql task details

MySQL database Event scheduled execution task details, mysql task details

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); optimize table jg_bj_comit_log; delete from jg_bj_order_create WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH); optimize table jg_bj_order_create; delete from jg_bj_order_match WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH); optimize table jg_bj_order_match; delete from jg_bj_order_cancel WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH ); optimize table jg_bj_order_cancel; delete from region WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH); optimize table jg_bj_operate_arrive; delete from region WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH); optimize table jg_bj_operate_depart; delete from jg_bj_operate_login WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH); optimize table jg_bj_operate_login; delete from jg_bj_operate_logout WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH); optimize table values; delete from jg_bj_operate_pay WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH ); optimize table partition; delete from jg_bj_position_driver WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH); optimize table jg_bj_position_driver; delete from partition WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH); optimize table partition; delete from jg_bj_rated_pasze WHERE created_on <SUBDATE (NOW (), INTERVAL 3 MONTH); optimize table jg_bj_rated_passenger; end // delimiter; #2. Enable event (to enable scheduled function, MySQL constant GlOBAL event_schduleer must be on or 1) show variables like 'event _ scheduler 'set global event_scheduler = '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;

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.