Basic functions of MySQL table partitioning

CREATE a partition TABLE www.2cto.com create table 'table name' ('equipmentid' char (17) not null, 'bubuteid' char (4) not null, 'value' varchar (20) not null, 'collecttime' datetime not null) ENGINE = InnoDB (applicable to most engines and can be adjusted as needed) default charset = gbk (encoding can be modified as needed) partition by range (to_days (COLLECTTIME) (PARTITION pmin values less than (to_days ('2017-01-01 ')), PARTITION p201001 values less than (to_days ('2017-02-01 ') ), PARTITION p201002 values less than (to_days ('2017-03-01 '), PARTITION p201003 values less than (to_days ('2017-04-01 ')), PARTITION p201004 values less than (to_days ('2017-05-01 '), PARTITION p201005 values less than (to_days ('2017-06-01 ')), PARTITION p201006 values less than (to_days ('2017-07-01 '), PARTITION p201007 values less than (to_days ('2017-08-01'), PARTITION p201008 VALUES LESS THAN (to_days ('2017-09-01 '), PARTITION p201009 values less than (to_days ('2017-10-01 ')), PARTITION p201010 values less than (to_days ('2017-11-01 '), PARTITION p201011 values less than (to_days ('2017-12-01 ')), PARTITION p201012 values less than (to_days ('1970-01-01 '), PARTITION p201101 values less than (to_days ('1970-02-01 ')), PARTITION p201102 values less than (to_days ('2017-03-01 '), PARTITIO N p201103 values less than (to_days ('2017-04-01 '), PARTITION p201104 values less than (to_days ('2017-05-01 ')), PARTITION p201105 values less than (to_days ('2017-06-01 '), PARTITION p201106 values less than (to_days ('2017-07-01 ')), PARTITION p201107 values less than (to_days ('2017-08-01 '), PARTITION p201108 values less than (to_days ('2017-09-01 ')), PARTITION p201109 values less than (to_days ('2017 1-10-01 '), PARTITION p201110 values less than (to_days ('2017-11-01'), PARTITION p201111 values less than (to_days ('2017-12-01 ')), PARTITION p201112 values less than (to_days ('2017-01-01 '), PARTITION pmax values less than maxvalue ); create a PARTITION for an existing table. alter table name: partition by range (to_days (COLLECTTIME) (PARTITION pmin values less than (to_days ('2017-01-01 ')), PARTITION p201001 values less than (to_d Ays ('2017-02-01 '), PARTITION p201002 values less than (to_days ('2017-03-01 ')), PARTITION p201003 values less than (to_days ('2017-04-01 '), PARTITION p201004 values less than (to_days ('2017-05-01 ')), PARTITION p201005 values less than (to_days ('2017-06-01 '), PARTITION p201006 values less than (to_days ('2017-07-01 ')), PARTITION p201007 values less than (to_days ('2017-08-01 '), PARTITION p20 1008 values less than (to_days ('2017-09-01 '), PARTITION p201009 values less than (to_days ('2017-10-01 ')), PARTITION p201010 values less than (to_days ('2017-11-01 '), PARTITION p201011 values less than (to_days ('2017-12-01 ')), PARTITION p201012 values less than (to_days ('1970-01-01 '), PARTITION p201101 values less than (to_days ('1970-02-01 ')), PARTITION p201102 values less than (to_days ('2017-0 03-01 '), PARTITION p201103 values less than (to_days ('2017-04-01'), PARTITION p201104 values less than (to_days ('2017-05-01 ')), PARTITION p201105 values less than (to_days ('2017-06-01 '), PARTITION p201106 values less than (to_days ('2017-07-01 ')), PARTITION p201107 values less than (to_days ('2017-08-01 '), PARTITION p201108 values less than (to_days ('2017-09-01'), PARTITION p201109 VALUES LESS T HAN (to_days ('2017-10-01 '), PARTITION p201110 values less than (to_days ('2017-11-01 ')), PARTITION p201111 values less than (to_days ('2017-12-01 '), PARTITION p201112 values less than (to_days ('2017-01-01'), PARTITION pmax values less than maxvalue ); if data already exists in the table, partitions are automatically stored, so you do not have to worry about data loss or manually classify data. delete the specified partition alter table Name drop partition name; append the table partition alter table Name drop partition pmax; alter table name ADD P ARTITION (PARTITION p201201 values less than (to_days ('2017-2-1 '), PARTITION pmax values less than maxvalue); view the PARTITION Information SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema () AND TABLE_NAME = 'table name'; check whether the query statement involves partition information... From table name where ...; This operation only displays partition information related to the query statement and does not return query results.