MySQL database Event scheduled execution task, mysqlevent

Source: Internet
Author: User

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;

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.