Mysql dynamically creates and deletes partition tables. mysql partition tables

Mysql dynamically creates and deletes partition tables. mysql partition tables

Due to project requirements, I recently studied how to dynamically create and delete partition tables in the mysql database. If all stored procedures are used, creating and deleting partition tables is logically rigid and inflexible, and error-prone. Therefore, I have created a data table table_fen_qu, which enables flexible management of partition tables.

When you create a partitioned table for the first time, if you add data from a partitioned table and data from a partitioned table, you can create a partition if the rang column value is greater than the maximum value of this column. Otherwise, the partition fails. Here, I put the code for creating a partition table together for execution (see the else statement segment in general_procedure ). The following is the operation process. For more information, see the official correction.

Step 1: Create a stored procedure.
The Stored Procedure Code for creating a partition table is as follows:

Drop procedure if exists general_procedure; -- Role of general_procedure: Create a partition table and store the relevant parameter -- general_procedure parameter when creating a partition table in table_fen_qu table: Table Name, the interval (in hours) between partition tables. create procedure general_procedure (in tablenamein varchar (50), in intervalHour int, in newIntervalNum int) general_pro: begin -- parameter: maximum time declare maxMonitTime datetime default SYSDATE (); -- parameter: String corresponding to the maximum time declare maxMonitTimeStr varchar (50); -- parameter: minimum time declare minMonitTime datetime default SYSDATE (); -- parameter: String corresponding to the maximum time declare minMonitTimeStr varchar (50); -- parameter: Database record count declare recoidNum int default 0; -- determines whether the input table name is null if tablenamein is null then leave general_pro; end if; -- determines the input time interval if intervalHour <= 0 then set intervalHour = 6; end if; -- determine the number of new partition tables if newIntervalNum <= 0 then set newIntervalNum = 1; end if; -- in this table, query the number of records that meet the conditions, backupflag = 0 indicates that select count (*) into recoidNum from table_fen_qu where tablename = tablenamein and backupflag = 0 is not backed up; if recoidNum> 0 then -- query the maximum monitoring time of the table in table_fen_qu select monittime into maxMonitTime from table_fen_qu where tablename = tablenamein and backupflag = 0 order by monittime desc limit 1; -- determines whether the monitoring time is null if maxMonitTime is null then set maxMonitTime = SYSDATE (); end if; -- compare the maximum time minus 72 hours and the system time in the morning and evening set recoidNum = timestampdiff (hour, SYSDATE (), DATE_SUB (maxMonitTime, INTERVAL 3 DAY )); -- If recoidNum is greater than 0, it indicates that the maximum monitoring time minus 72 hours is still after the system time. -- it indicates that a new partition table is not required. Otherwise, after the maximum monitoring time is set up, newIntervalNum partitions separated by every intervalHour hour if recoidNum <= 0 then set recoidNum = 1; while recoidNum <= newIntervalNum do set maxMonitTime = ADDDATE (maxMonitTime, INTERVAL intervalHour HOUR); set maxMonitTimeStr = CONCAT ('P', DATE_FORMAT (maxMonitTime, "% Y % m % d % H % I % s ")); -- start to ADD a partition table -- splice the partition table code segment set @ v_add_s = CONCAT ('alter table', tablenamein, 'add PARTITION (PARTITION ', maxMonitTimeStr, 'values less (\'', maxMonitTime, '\') ENGINE = InnoDB) '); -- defines the pre-processing statement prepare stmt from @ v_add_s; -- executes the pre-processing statement execute stmt; -- releases the pre-processing statement deallocate prepare stmt; -- start to add the record insert into table_fen_qu (fenquname, tablename, monittime, backupflag) VALUES (maxMonitTimeStr, tablenamein, maxMonitTime, 0) in table_fen_qu ); -- add 1 set recoidNum = recoidNum + 1; end while; end if; else set recoidNum = 2; -- calculate the minimum time set minMonitTimeStr = CONCAT (DATE_FORMAT (DATE_SUB (maxMonitTime, INTERVAL 60 DAY), '% Y-% m-% D'), '00:00:00'); set minMonitTime = STR_TO_DATE (minMonitTimeStr, '% Y-% m-% d % H: % I: % s'); -- calculate the maximum time of hovertree.com set maxMonitTimeStr = CONCAT (DATE_FORMAT (ADDDATE (maxMonitTime, INTERVAL 4 DAY), '% Y-% m-% D'), '00:00:00'); set maxMonitTime = STR_TO_DATE (maxMonitTimeStr, '% Y-% m-% d % H: % I: % s'); -- calculates the number of partitions in the new table. set newIntervalNum = floor (timestampdiff (hour, minMonitTime, maxMonitTime) /intervalHour) + 1; if newIntervalNum <12 then set newIntervalNum = 12; end if; -- delete all TABLE partitions set @ v_del_s = CONCAT ('alter table', tablenamein, 'Remove partitioning'); -- defines the pre-processing statement prepare stmt from @ v_del_s; -- executes the pre-processing statement execute stmt; -- releases the pre-processing statement deallocate prepare stmt; -- delete all data delete from table_fen_qu where tablename = tablenamein; -- create a partition -- set the time set minMonitTimeStr = CONCAT ('P', DATE_FORMAT (minMonitTime, "% Y % m % d % H % I % s"); -- concatenates and adds a partition TABLE SQL set @ v_add_s = CONCAT ('alter table', tablenamein, 'partition by range columns (moint_time) (PARTITION ', minMonitTimeStr, 'values less than (\ '', minMonitTime,' \ ') ENGINE = InnoDB ,'); -- start to add the record insert into table_fen_qu (fenquname, tablename, monittime, backupflag) VALUES (minMonitTimeStr, tablenamein, minMonitTime, 0) in table_fen_qu ); while recoidNum <= newIntervalNum do -- set the time set minMonitTime = ADDDATE (minMonitTime, INTERVAL intervalHour HOUR); set minMonitTimeStr = CONCAT ('P', DATE_FORMAT (minMonitTime, "% Y % m % d % H % I % s"); -- concatenates and adds a PARTITION table SQL set @ v_add_s = CONCAT (@ v_add_s, 'partition', minMonitTimeStr, 'values less than (\ '', minMonitTime, '\') ENGINE = InnoDB, '); -- add the record insert into table_fen_qu (fenquname, tablename, monittime, backupflag) VALUES (minMonitTimeStr, tablenamein, minMonitTime, 0); -- Record count plus 1 set recoidNum = recoidNum + 1; end while; set @ v_add_s = left (@ v_add_s, LENGTH (@ v_add_s)-1); set @ v_add_s = CONCAT (@ v_add_s, '); -- Define the pre-processing statement prepare stmt from @ v_add_s; -- execute the pre-processing statement http://www.cnblogs.com/roucheng/ execute stmt; -- release the pre-processing statement deallocate prepare stmt; end if; end general_pro;

Step 2: Create an event plan and regularly execute the event.
The event is as follows:

-- Enable the event plan set global event_scheduler = ON;/* Create a scheduled execution interval from the start time */drop event if exists eachDayEvent; DELIMITER | create event eachDayEvent on schedule every 1 day starts '2017-05-01 00:00:00 'on completion preserve enabledo begin -- general_procedure parameter: Table Name, time Interval between partition tables (unit: hour ), number of partition tables to be added-non-energy consumption 5 Minutes clock-interval 6 hours-6 h/Partition Table hovertree.com call general_procedure ('no _ energy_five_minute_data ', 6, 8 ); -- call general_procedure ('temp _ data', 6, 8) from the original data table-6 hours-6 h/partitioned table ); -- five-minute-interval-24 h/Partition Table call general_procedure ('energy _ five_minute_data ', 24, 4 ); -- five-minute energy consumption Summary-daily intervals-24 h/partitioned Table call general_procedure ('energy _ five_minute_data_summarize ', 24, 4 ); -- How to call the energy consumption hour table-interval week-7*24 h/Partition Table call general_procedure ('energy _ hour_data_summarize ); -- call general_procedure ('energy _ item_five_minute_data ', 4) of the energy Consumption classification sub-item 5-7*24 h of the interval week ); -- energy consumption classification sub-hour table-interval quarter-90*24 h/Partition Table call general_procedure ('energy _ item_hour_data ', 4 ); -- energy consumption daily summary table-interval of six months-4380 h/Partition Table call general_procedure ('energy _ day_data_summarize ', 4380, 4); -- delete the backed up Partition Table http://www.cnblogs.com/roucheng/ call del_fenqu (); end | DELIMITER;

Http://www.cnblogs.com/roucheng/p/mysqlfenqu.html