MySQL partition table and auto-create partition partition

The Range Partition Table build Table statement is as follows, where the partition key must and ID form the primary key and the unique key

CREATE TABLE ' test1 ' (  ' id ' char (+) COLLATE utf8mb4_unicode_ci not NULL COMMENT ' self-increment primary key (GUID) ',  ' create_time ' time Stamp NOT null DEFAULT current_timestamp COMMENT ' creation time ',  ' partition_key ' int (8) NOT null COMMENT ' partition key (format: YYYYMMDD) ', C3/>primary key (' id ', ' partition_key '),  UNIQUE key ' Id_unique ' (' id ', ' partition_key ')) Engine=innodb DEFAULT Charset=utf8mb4 collate=utf8mb4_unicode_cipartition by RANGE (partition_key) (partition P0 VALUES less THAN (20180619) ENGINE = InnoDB, PARTITION p20180619 values less THAN (20180620) ENGINE = InnoDB, PARTITION p20180621 values less THAN (20 180622) engine = InnoDB, PARTITION p20180622 values less THAN (20180623) engine = InnoDB, PARTITION p20180623 values Less THAN (20180624) ENGINE = InnoDB);

New Partition

Alter TABLE ' test1 ' Add PARTITION (PARTITION p20180629 VALUES less THAN (20180630) ENGINE = InnoDB);

Delete Partition

ALTER TABLE ' test1 '  drop PARTITION p20180629;

MySQL does not automatically create partitions, it needs to automatically create partitions using MySQL event events

1. The stored procedure for creating the partition is as follows (each execution verifies that the current partition exists and does not process if it exists; it is created if it does not exist):

DELIMITER $$ #该表所在数据库名称USE ' demo ' $ $DROP PROCEDURE IF EXISTS ' create_partition_by_day ' $ $CREATE PROCEDURE ' Create_ Partition_by_day ' (in_schemaname varchar, in_tablename varchar) BEGIN #当前日期存在的分区的个数 DECLARE rows_cnt INT Unsig    NED;    #目前日期, DECLARE target_date TIMESTAMP for the day after the current date;    #分区的名称, formatted as p20180620 DECLARE partitionname VARCHAR (9);    #当前分区名称的分区值上限, i.e. PartitionName + 1 DECLARE partition_add_day VARCHAR (9);    SET target_date = Now () + INTERVAL 1 day;    SET partitionname = date_format (target_date, ' p%y%m%d ');    SET target_date = target_date + INTERVAL 1 day;    SET partition_add_day = date_format (target_date, '%y%m%d '); SELECT COUNT (*) into rows_cnt from information_schema.partitionswhere Table_schema = in_schemaname and table_name = IN_TAB    Lename and partition_name = PartitionName; IF rows_cnt = 0 Then SET @SQL = CONCAT (' ALTER TABLE ', In_schemaname, '. ', ' in_tablename, ' ', ' ADD partitio N (PARTITION ', PartitionName, "VALUES less THAN (",            Partition_add_day, ") ENGINE = InnoDB);        PREPARE STMT from @SQL;        EXECUTE STMT;     Deallocate PREPARE STMT; ELSE SELECT CONCAT ("Partition", PartitionName, "' for Table '", In_schemaname, ".", In_tablename, "' already exists") A     S result; END IF; end$ $DELIMITER;

2. Database timed tasks (performed once per hour)

DELIMITER $$ #该表所在的数据库名称USE ' demo ' $ $CREATE EVENT IF not EXISTS ' daily_generate_partition ' on SCHEDULE every 1 hour   #执行周 Period, there are days, months, etc. starts ' 2018-06-20 00:00:00 ' on completion preserveenablecomment ' Creating partitions ' do BEGIN    # Call the stored procedure you just created, the first parameter is the database name, the second parameter is the table name called    datacollectcenter.create_partition_by_day (' demo ', ' test1 '); end$ $DELIMITER;

　　

MySQL partition table and auto-create partition partition