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