MySQL Partition Table Management

MySQL Partition Table Management

I. How to manage RANGE and LIST partitions

This partitioned table is used as an example.

CREATE TABLE members (    id INT,    fname VARCHAR(25),    lname VARCHAR(25),    dob DATE)PARTITION BY RANGE( YEAR(dob) ) (    PARTITION p0 VALUES LESS THAN (1970),    PARTITION p1 VALUES LESS THAN (1980),    PARTITION p2 VALUES LESS THAN (1990));

1. delete partitions

ALTER TABLE members DROP PARTITION p1;

Note: If a partition is deleted, all data in the partition will be lost. In addition, use show create table members \ G; command to view the table creation statement, you will not be able to see any information about the deleted partition.

For a RANGE partition, if the p1 partition is deleted, when data is inserted, if the date is within the RANGE of 1970 to 1980, the data will be allocated to the next partition, that is, p2 ,.

For LIST partitions, if a partition is deleted, an error is returned if the data belongs to this partition during data insertion.

Use the truncate command to delete data without deleting the partition information.

ALTER TABLE members TRUNCATE PARTITION p1;

2. Add partitions

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

Note: You can use the ADD command to ADD partitions. You can only ADD partitions at the end of the partition list. In this example, you can only ADD partitions after January 1, 1990.

Of course, in the actual production environment, such limitations are too great. For example, I want to add a partition before the p0 partition, with a range of 1960, or add another 1975 partition between p1, at this time, the use of ADD will not be able to meet such requirements, you can use alter table... reorganize partition command.

For example:

ALTER TABLE members REORGANIZE PARTITION p0 INTO (    PARTITION s0 VALUES LESS THAN (1960),    PARTITION s1 VALUES LESS THAN (1970));

The REORGANIZE command is actually quite flexible. It can not only split partitions, but also be used to merge partitions, for example:

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (    PARTITION m0 VALUES LESS THAN (1980),    PARTITION m1 VALUES LESS THAN (2000));

Note:

1> you cannot use the reorganize partition command to modify the PARTITION type of a TABLE. You can only use the alter table... partition by... statement, for example:

ALTER TABLE members    PARTITION BY HASH( YEAR(dob) )    PARTITIONS 8;

2> reorganize partition Syntax:

ALTER TABLE tbl_name    REORGANIZE PARTITION partition_list    INTO (partition_definitions);

The partition range in partition_definitions must cover the partition range in partition_list.

Ii. How to manage HASH and KEY partitions

This partitioned table is used as an example.

CREATE TABLE clients (    id INT,    fname VARCHAR(30),    lname VARCHAR(30),    signed DATE)PARTITION BY HASH( MONTH(signed) )PARTITIONS 12;

For HASH partitions and KEY partitions, the preceding RANGE and LIST partitions are not supported, such as DROP, TRUNCATE, and REORGANIZE partitions.

In fact, it only supports one type of "partition adjustment ".

ALTER TABLE clients COALESCE PARTITION 4;

This command is used to crop four clients table partitions from 12 to 8.

ALTER TABLE clients ADD PARTITION PARTITIONS 6;

Similarly, this command adds 6 partitions to the clients table, from 12 to 18.

For more details, please continue to read the highlights on the next page:

1 2 Next Page