MySQL dynamically creates and deletes a partition table

MySQL dynamically creates and deletes a partition table

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;
-- General_procedure: Creates a partition table and stores related parameters in the table_fen_qu table.
-- General_procedure parameter: Table Name, time interval between partition tables (unit: hour), number of partition tables to be added
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: number of database records
Declare recoidNum int default 0;
-- Determines whether the input table name is null
If tablenamein is null then
Leave general_pro;
End if;
-- Determine the input 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, the number of records that meet the condition is queried. backupflag = 0 indicates that no backup is performed.
Select count (*) into recoidNum from table_fen_qu where tablename = tablenamein and backupflag = 0;
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;
-- Determine whether the monitoring time is null
If maxMonitTime is null then
Set maxMonitTime = SYSDATE ();
End if;
-- Compare the maximum time minus the time after 72 hours and the system time sooner or later
Set recoidNum = timestampdiff (hour, SYSDATE (), DATE_SUB (maxMonitTime, INTERVAL 3 DAY ));
-- If recoidNum is greater than 0, the maximum monitoring time minus 72 hours is still after the system time,
-- You do not need to create a new partition table. Otherwise, newIntervalNum partition tables separated by every intervalHour hour after the maximum monitoring time is set up.
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 adding a partition table
-- Splice the Partition Table code segment
Set @ v_add_s = CONCAT ('alter table', tablenamein, 'add PARTITION (PARTITION ', maxMonitTimeStr, 'values less than (\ '', maxMonitTime ,'\') ENGINE = InnoDB )');
-- Define a pre-processing statement
Prepare stmt from @ v_add_s;
-- Execute the pre-processing statement
Execute stmt;
-- Release the pre-processing statement
Deallocate prepare stmt;
-- Start adding record in table_fen_qu
Insert into table_fen_qu (fenquname, tablename, monittime, backupflag) VALUES (maxMonitTimeStr, tablenamein, maxMonitTime, 0 );
-- Record count plus 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 ');
-- Calculate 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 ');
-- Define a pre-processing statement
Prepare stmt from @ v_del_s;
-- Execute the pre-processing statement
Execute stmt;
-- Release 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 "));
-- Concatenate and add a partition table SQL statement
Set @ v_add_s = CONCAT ('alter table', tablenamein, 'partition by range columns (moint_time) (PARTITION ', minMonitTimeStr, 'values less than (\ '', minMonitTime, '\') ENGINE = InnoDB ,');
-- Start adding record in table_fen_qu
Insert into table_fen_qu (fenquname, tablename, monittime, backupflag) VALUES (minMonitTimeStr, tablenamein, minMonitTime, 0 );
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 "));
-- Concatenate and add a partition table SQL statement
Set @ v_add_s = CONCAT (@ v_add_s, 'partition', minMonitTimeStr, 'values less than (\ '', minMonitTime, '\') ENGINE = InnoDB ,');
-- Start adding record in table_fen_qu
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 a pre-processing statement
Prepare stmt from @ v_add_s;
-- Execute the pre-processing statement