Mysql dynamically creates and deletes a partition table

Mysql dynamically creates and deletes partition tables because of project requirements. Recently, I have 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. 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: 001 drop procedure if exists general_procedure; 002 -- general_procedure: create a partition table and store the related parameter 003 -- general_procedure when creating a partition table in the table_fen_qu table: Table Name, interval between partition tables (in hours ), number of partition tables to be added 004 create procedure general_procedure (in tablenamein varchar (50), in intervalHour int, in newIntervalNum int) 005general_pro: begin006 -- parameter: maximum time 007 declare maxMonitTime datetime default SYSDATE (); 008 -- parameter: String 009 de corresponding to the maximum time Clare maxMonitTimeStr varchar (50); 010 -- parameter: Minimum Time 011 declare minMonitTime datetime default SYSDATE (); 012 -- parameter: String 013 declare minMonitTimeStr varchar (50) corresponding to the maximum time ); 014 -- parameter: database records 015 declare recoidNum int default 0; 016 -- determines whether the input table name is null 017 if tablenamein is null then018 leave general_pro; 019 end if; 020 -- determine the input time interval 021 if intervalHour <= 0 then022 set intervalHour = 6; 023 end if; 024 -- determine the number of new partition tables 025 if ne WIntervalNum <= 0 then026 set newIntervalNum = 1; 027 end if; 028 029 -- query the number of qualified records in this table, backupflag = 0 indicates that no backup 030 select count (*) into recoidNum from table_fen_qu where tablename = tablenamein and backupflag = 0; 031 if recoidNum> 0 then032 -- query the maximum monitoring time of the table in table_fen_qu table 033 select monittime into maxMonitTime from region where tablename = tablenamein and backupflag = 0 order by monittime desc limit 1; 034- -Determine whether the monitoring time is null035 if maxMonitTime is null then036 set maxMonitTime = SYSDATE (); 037 end if; 038 -- compare the maximum time minus 72 hours and the system time in the morning and evening 039 set recoidNum = timestampdiff (hour, SYSDATE (), DATE_SUB (maxMonitTime, INTERVAL 3 DAY )); 040 -- if the recoidNum value is greater than 0, it indicates that the maximum monitoring time minus 72 hours is still after the system time. 041 -- it indicates that a new partition table is not required. Otherwise, then, after the maximum monitoring time is set up, newIntervalNum partitions with intervals of every intervalHour hour are 042 if recoidNum <= 0 then043 set recoidNum = 1; 044 while recoidNu M <= newIntervalNum do045 set maxMonitTime = ADDDATE (maxMonitTime, INTERVAL intervalHour HOUR); 046 set maxMonitTimeStr = CONCAT ('P', DATE_FORMAT (maxMonitTime, "% Y % m % d % H % I % s ")); 047 -- add partition table 048/* splice partition table code segment */049 set @ v_add_s = CONCAT ('alter table', tablenamein, 'add PARTITION (PARTITION ', maxMonitTimeStr, 'values less than (\ '', maxMonitTime, '\') ENGINE = InnoDB) '); 050/* define preprocessing statement */051 prepare stmt from @ V_add_s; 052/* execute preprocessing statement */053 execute stmt; 054/* release preprocessing statement */055 deallocate prepare stmt; 056 -- add the record 057 insert into table_fen_qu (fenquname, tablename, monittime, backupflag) VALUES (maxMonitTimeStr, tablenamein, maxMonitTime, 0) in table_fen_qu ); 058 -- Record count plus 1059 set recoidNum = recoidNum + 1; 060 end while; 061 end if; 062 else063 set recoidNum = 1; 064 -- Minimum computing time 065 set minMonitTimeStr = CONCAT (DATE_FORMAT (DATE_SU B (maxMonitTime, INTERVAL 60 DAY), '% Y-% m-% D'), '00:00:00'); 066 set minMonitTime = STR_TO_DATE (minMonitTimeStr, '% Y-% m-% d % H: % I: % s'); 067 -- calculate the maximum time 068 set maxMonitTimeStr = CONCAT (DATE_FORMAT (ADDDATE (maxMonitTime, INTERVAL 4 DAY), '% Y-% m-% D'), '00:00:00'); 069 set maxMonitTime = STR_TO_DATE (maxMonitTimeStr, '% Y-% m-% d % H: % I: % s'); 070 -- calculate the number of partitions in the new table 071 set newIntervalNum = floor (timestampdiff (hour, minMonitTime, MaxMonitTime)/intervalHour) + 1; 072 if newIntervalNum <10 then073 set newIntervalNum = 10; 074 end if; 075 -- delete all TABLE partitions 076 set @ v_del_s = CONCAT ('alter table', tablenamein, 'remove partitioning '); 077/* define preprocessing statement */078 prepare stmt from @ v_del_s; 079/* execute preprocessing statement */080 execute stmt; 081/* release preprocessing statement */082 deallocate prepare stmt; 083 -- delete all data 084 delete from table_fen_qu where tablename = tablenamein; 085 -- create partition 0 86 while recoidNum <= newIntervalNum do087 set minMonitTimeStr = CONCAT ('P', DATE_FORMAT (minMonitTime, "% Y % m % d % H % I % s ")); 088 -- add partition table 089/* splice partition table code segment */090 if recoidNum = 1 then091 set @ v_add_s = CONCAT ('alter table', tablenamein, 'partition by range columns (moint_time) (PARTITION ', minMonitTimeStr, 'values less than (\ '', minMonitTime,' \ ') ENGINE = InnoDB)'); 092 else093 set @ v_add_s = CONCAT ('alter T ABLE ', tablenamein, 'add PARTITION (PARTITION', minMonitTimeStr, 'values less than (\ '', minMonitTime, '\') ENGINE = InnoDB) '); 094 end if; 095/* define preprocessing statement */096 prepare stmt from @ v_add_s; 097/* execute preprocessing statement */098 execute stmt; 099/* release preprocessing statement */100 deallocate prepare stmt; 101 -- start adding record 102 insert into table_fen_qu (fenquname, tablename, monittime, backupflag) VALUES (minMonitTimeStr, tablenamein, minMonit Time, 0); 103 -- number of records plus 1104 set recoidNum = recoidNum + 1; 105 set minMonitTime = ADDDATE (minMonitTime, INTERVAL intervalHour HOUR); 106 end while; 107 end if; delete end general_pro; the Stored procedure for deleting a partitioned table is as follows: 01 drop procedure if exists del_fenqu; 02 -- delete 'temp _ data', 'no _ energy_five_minute_data ', 'Energy _ five_minute_data 'and 'energy _ five_minute_data_summarize' have backed up partition tables 03 create procedure del_fenqu () 04del_fq: begin05 -- parameter: Record id0 6 declare myrecid int; 07 -- parameter: Partition Table Name 08 declare myfenquname varchar (50); 09 -- parameter: name of the table in which the table partition is located 10 declare mytablename varchar (50 ); 11 -- parameter: Database record count 12 declare recoidNum int default 0; 13 -- in this table, query the number of qualified records. backupflag = 1 indicates that 14 select count (*) has been backed up (*) into recoidNum from table_fen_qu where tablename in ('temp _ data', 'no _ energy_five_minute_data ', 'Energy _ five_minute_data', 'Energy _ Region') and backu Pflag = 1; 15 -- delete all records that meet the condition 16 while recoidNum> 0 do17 -- Query an 18 select recid, fenquname, tablename into myrecid, myfenquname, mytablename from table_fen_qu where tablename in ('temp _ data', 'no _ energy_five_minute_data ', 'Energy _ five_minute_data', 'Energy _ five_minute_data_summarize ') and backupflag = 1 order by monittime desc limit 1; 19 -- delete data record 20 delete from table_fen_qu WHERE recid = myrecid; 21 -- delete table points PARTITION 22/* partition table code segment */23 set @ v_drop_d = CONCAT ('alter table', mytablename, 'drop partition', myfenquname ); 24/* define the pre-processing statement */25 prepare stmt from @ v_drop_d; 26/* execute the pre-processing statement */27 execute stmt; 28/* release the pre-processing statement */29 deallocate prepare stmt; 30 -- in this table, query the number of qualified records. backupflag = 1 indicates that 31 select count (*) into recoidNum from table_fen_qu where tablename in ('temp _ data ', 'No _ energy_five_minute_data ', 'Energy _ five_minute_data', 'ener Gy_five_minute_data_summarize ') and backupflag = 1; 32 end while; 33end del_fq; Step 2: Create an event plan and regularly execute the event. The event is as follows: 01 -- open event plan 02 set global event_scheduler = ON; 03/* create scheduled execution every one day from the start time */04 drop event if exists eachDayEvent; 05 DELIMITER | 06 create event eachDayEvent07 on schedule every 1 day starts '2017-05-01 00:00:00 '08 on completion preserve enable09do10 begin11 -- general_procedure parameter: Table Name, time Interval between partition tables (unit: hour ), the number of partition tables to be added is 12-non-energy consumption 5 Minutes clock-interval 6 hours-6 h/Partition Table 13 call general_procedure ('no _ energy_five_minute_data ', 6, 8 ); 14 -- original data table-interval of 6 hours-6 h/Partition Table 15 call general_procedure ('temp _ data', 6, 8 ); 16 -- five-minute energy consumption-one-day interval-24 h/Partition Table 17 call general_procedure ('energy _ five_minute_data ', 24, 4 ); 18 -- five-minute energy consumption Summary-day interval-24 h/Partition Table 19 call general_procedure ('energy _ five_minute_data_summarize ', 24, 4 ); 20 -- energy consumption hour Table call-interval week-7*24 h/Partition Table 21 call general_procedure ('energy _ hour_data_summarize ', 168, 4 ); 22 -- energy consumption classification sub-item 5 minute clock-7*24 h in the interval week/Partition Table 23 call general_procedure ('energy _ item_five_minute_data ', 168,4 ); 24 -- energy consumption classification sub-hour table-interval quarter-90*24 h/Partition Table 25 call general_procedure ('energy _ item_hour_data ', 216, 4 ); 26 -- energy consumption summary table-interval of six months-4380 h/Partition Table 27 call general_procedure ('energy _ day_data_summarize ', 4380, 4); 28 -- delete the backed up Partition Table 29 call del_fenqu (); 30 end | 31 DELIMITER; 32