MySQL CREATE TABLE partition periodically

MySQL CREATE TABLE partition periodically

I. Stored Procedures-Table partitioning
-----------------------------------------------------------------
Demand:
Create a partition every month
Partition name name format: p201201,p201202 ...
------------------------------------------

1 CREATEDefiner=' Root ' @ '%`PROCEDURE' Auto_create_partition ' (inch' DatabaseName 'varchar( -),inch' TableName 'varchar( -))2L_end:BEGIN 3 # Initialize Variables4 DECLAREMax_partition_descriptionVARCHAR(255)DEFAULT 0; 5 DECLAREP_descriptionVARCHAR(255)DEFAULT 0; 6 DECLAREIINT DEFAULT 1; 7 DECLAREIsexist_partitionVARCHAR(255)DEFAULT 0; 8 DECLARENext_p_namevarchar(255)DEFAULT 0;9# query Table partition for the last one months of the partition (ORDER  byPartition_descriptionDESCLIMIT1)Ten SELECTPartition_name intoIsexist_partition fromInformation_schema. PartitionsWHERETable_schema=DatabaseName andtable_name=TableNameORDER  byPartition_descriptionDESCLIMIT1 ;  One  A IFIsexist_partition<=>"" Then  - SELECT"PartitionTable  not  isExist as"*****ERROR*****";  - LEAVE l_end; the END IF; -  - # Set the name of the partition to be created according to the time - SELECTConcat'P', Date_format (Date_add (now (), INTERVAL1 MONTH),'%y%m')) intoNext_p_name; +  - # Determine if the partition to be created exists + IFIsexist_partition=Next_p_name Then A SELECTConcat ("Partition (", Next_p_name, ") isExist ") as"*****INFO*****";  at LEAVE l_end; - END IF; -  - # The maximum value of the most recent partition set (right boundary) - SELECTPartition_description intoMax_partition_description fromInformation_schema. PartitionsWHERETable_schema=DatabaseName andtable_name=TableNameORDER  byPartition_descriptionDESCLIMIT1;  -  in IFMax_partition_description<=>"" Then  - SELECT"PartitionTable  isError as"*****ERROR*****";  to LEAVE l_end; + END IF; -  the # Create a new partition *#1. Set the right boundary value for the new partition $ SETP_description=To_days (Date_format (Date_add (now), INTERVAL2 MONTH),'%y%m01')); Panax Notoginseng#2. Splicing SQL statements for new partitions - SET @S=CONCAT ('ALTER TABLE', TableName,'ADD PARTITION (PARTITION', Next_p_name,'VALUES less THAN (', P_description,'))');  the SELECT @S; +#3. Using preprocessing to execute SQL A#------------------------------------------------------------------------------------------ the # Syntax: syntax +#PREPAREStatement_name fromSql_text/*definition*/  -#EXECUTEStatement_name[USING variable [, variable ...]]/*executing a preprocessing statement*/  $#deallocate PREPAREStatement_name/*Delete Definition*/  $#------------------------------------------------------------------------------------------ - PREPAREStmt2 from @S; - EXECUTEstmt2; the deallocate PREPAREstmt2; - Wuyi ENDL_end

-----------------------------------------------------------------------

Two. Timed event Creation

MySQL creates table partitions periodically