Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to achieve table partitioning in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

The functional basis of MySQL partitioning

Create a partition table

CREATE TABLE `Table name `(

`EQUIPMENTID` char (17) NOT NULL

`ATTRIBUTEID` char (4) NOT NULL

`VALUE` varchar (20) NOT NULL

`COLLECTTIME` datetime NOT NULL

)

ENGINE=InnoDB (for most engines, can be adjusted as needed)

DEFAULT CHARSET=gbk (the code can be modified as needed)

PARTITION BY RANGE (to_days (COLLECTTIME))

(PARTITION pmin VALUES LESS THAN (to_days ('2010-01-01'))

PARTITION p201001 VALUES LESS THAN (to_days ('2010-02-01'))

PARTITION p201002 VALUES LESS THAN (to_days ('2010-03-01'))

PARTITION p201003 VALUES LESS THAN (to_days ('2010-04-01'))

PARTITION p201004 VALUES LESS THAN (to_days ('2010-05-01'))

PARTITION p201005 VALUES LESS THAN (to_days ('2010-06-01'))

PARTITION p201006 VALUES LESS THAN (to_days ('2010-07-01'))

PARTITION p201007 VALUES LESS THAN (to_days ('2010-08-01'))

PARTITION p201008 VALUES LESS THAN (to_days ('2010-09-01'))

PARTITION p201009 VALUES LESS THAN (to_days ('2010-10-01'))

PARTITION p201010 VALUES LESS THAN (to_days ('2010-11-01'))

PARTITION p201011 VALUES LESS THAN (to_days ('2010-12-01'))

PARTITION p201012 VALUES LESS THAN (to_days ('2011-01-01'))

PARTITION p201101 VALUES LESS THAN (to_days ('2011-02-01'))

PARTITION p201102 VALUES LESS THAN (to_days ('2011-03-01'))

PARTITION p201103 VALUES LESS THAN (to_days ('2011-04-01'))

PARTITION p201104 VALUES LESS THAN (to_days ('2011-05-01'))

PARTITION p201105 VALUES LESS THAN (to_days ('2011-06-01'))

PARTITION p201106 VALUES LESS THAN (to_days ('2011-07-01'))

PARTITION p201107 VALUES LESS THAN (to_days ('2011-08-01'))

PARTITION p201108 VALUES LESS THAN (to_days ('2011-09-01'))

PARTITION p201109 VALUES LESS THAN (to_days ('2011-10-01'))

PARTITION p201110 VALUES LESS THAN (to_days ('2011-11-01'))

PARTITION p201111 VALUES LESS THAN (to_days ('2011-12-01'))

PARTITION p201112 VALUES LESS THAN (to_days ('2012-01-01'))

PARTITION pmax VALUES LESS THAN MAXVALUE)

Create a partition for an existing table

Alter table table name

PARTITION BY RANGE (to_days (COLLECTTIME))

(PARTITION pmin VALUES LESS THAN (to_days ('2010-01-01'))

PARTITION p201001 VALUES LESS THAN (to_days ('2010-02-01'))

PARTITION p201002 VALUES LESS THAN (to_days ('2010-03-01'))

PARTITION p201003 VALUES LESS THAN (to_days ('2010-04-01'))

PARTITION p201004 VALUES LESS THAN (to_days ('2010-05-01'))

PARTITION p201005 VALUES LESS THAN (to_days ('2010-06-01'))

PARTITION p201006 VALUES LESS THAN (to_days ('2010-07-01'))

PARTITION p201007 VALUES LESS THAN (to_days ('2010-08-01'))

PARTITION p201008 VALUES LESS THAN (to_days ('2010-09-01'))

PARTITION p201009 VALUES LESS THAN (to_days ('2010-10-01'))

PARTITION p201010 VALUES LESS THAN (to_days ('2010-11-01'))

PARTITION p201011 VALUES LESS THAN (to_days ('2010-12-01'))

PARTITION p201012 VALUES LESS THAN (to_days ('2011-01-01'))

PARTITION p201101 VALUES LESS THAN (to_days ('2011-02-01'))

PARTITION p201102 VALUES LESS THAN (to_days ('2011-03-01'))

PARTITION p201103 VALUES LESS THAN (to_days ('2011-04-01'))

PARTITION p201104 VALUES LESS THAN (to_days ('2011-05-01'))

PARTITION p201105 VALUES LESS THAN (to_days ('2011-06-01'))

PARTITION p201106 VALUES LESS THAN (to_days ('2011-07-01'))

PARTITION p201107 VALUES LESS THAN (to_days ('2011-08-01'))

PARTITION p201108 VALUES LESS THAN (to_days ('2011-09-01'))

PARTITION p201109 VALUES LESS THAN (to_days ('2011-10-01'))

PARTITION p201110 VALUES LESS THAN (to_days ('2011-11-01'))

PARTITION p201111 VALUES LESS THAN (to_days ('2011-12-01'))

PARTITION p201112 VALUES LESS THAN (to_days ('2012-01-01'))

PARTITION pmax VALUES LESS THAN MAXVALUE)

If there is already data in the table, it will be partitioned and stored automatically. You don't have to worry about data loss or manual data classification.

Delete the specified partition in the table

ALTER TABLE table name DROP PARTITION partition name

Add table partition

ALTER TABLE table name DROP PARTITION pmax

ALTER TABLE table name

ADD PARTITION (

PARTITION p201201 VALUES LESS THAN (to_days ('2012-2-1'))

PARTITION pmax VALUES LESS THAN MAXVALUE)

View label 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'

View the partition information involved in the query statement

Explain partitions

Select... From table name where...

This operation only looks at the partition information related to the query statement and does not return the query result.

After reading the above, do you have any further understanding of how to implement table partitioning in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.