MySQL Auto partition

Automatic partitioning requires the event scheduler in MySQL to be turned on, and you can see if the scheduler is turned on by the following command

 like ' %scheduler% ';

If not, open it with the following command

SET = 1;

1. Create a partitioned table

CREATE TABLESales (IDINTauto_increment, AmountDOUBLE  not NULL, CreatetimeDATETIME  not NULL,    PRIMARY KEY(ID, createtime)) ENGINE=innodbpartition byRANGE ( Year(Createtime)) (PARTITION p_2010VALUESLess THAN ( .), #2011年以前一个分区 PARTITION p_2011VALUESLess THAN ( -) # .-20,121 partitions)

To view the current table partition information:

   SELECT partition_name,partition_description,partition_method,table_rows,create_time     from information_schema. Partitions   WHERE='demo'and= ' Sales ' ORDER  by DESC ;

Create a new stored procedure to add a partition to a partitioned table

DROP PROCEDURE IF EXISTScreate_partition;CREATE PROCEDURECreate_partition (inchDatabaseNameVARCHAR( -),inchTableNameVARCHAR( -),inchPartitionnameprefixVARCHAR( -)) L_end:BEGIN     DECLAREMax_partition_descriptionVARCHAR(255)DEFAULT ""; DECLAREP_nameVARCHAR(255)DEFAULT ""; DECLAREP_descriptionVARCHAR(255)DEFAULT ""; DECLAREIINT DEFAULT 1; DECLAREIsexist_partitionVARCHAR(255)DEFAULT ""; DECLAREPartition_countINT DEFAULT 1; #一次性增加一个分区数SELECTIfnull (Partition_name, "") intoIsexist_partition frominformation_schema. PartitionsWHERETable_schema=DatabaseName andtable_name=TableName LIMIT1 ; IFIsexist_partition<=>"" Then       SELECT"PartitionTable  not  isExist as"*****ERROR*****";     LEAVE L_end; END IF; SELECTIfnull (Partition_description, "") intoMax_partition_description frominformation_schema. PartitionsWHERETable_schema=DatabaseName andtable_name=TableNameORDER  byPartition_descriptionDESCLIMIT1; IFMax_partition_description<=>"" Then       SELECT"PartitionTable  isError as"*****ERROR*****";     LEAVE L_end; END IF; SETMax_partition_description= REPLACE(Max_partition_description,'\"', "');           While I <= partition_count do SET p_description = max_partition_description+i; SET p_name = REPLACE (p_description,'-', "'); SET @s=concat ('ALTER TABLE ', TableName,' ADDPARTITION (PARTITION', Partitionnameprefix,p_name,' VALUESLess THAN (', P_description,'))');           SELECT @S;           PREPARE stmt2 from @S;           EXECUTE stmt2;           Deallocate PREPARE stmt2;    SET i = i + 1;          END while; END L_end;

Create an event timer to create a partition on a timed basis

DELIMITER $$CREATEEVENT auto_set_partitions onSCHEDULE every5MINUTE COMMENT'Auto set partitions for table' DoBEGINCall create_partition ('MySQL','Test','P'); END$$

This will create a new partition on the Sales partition table every 5 minutes.

MySQL Auto partition