MySQL partition table Management to

First, how to manage the range and list partition

Take the partition table for example

CREATE TABLEMembers (IDINT, fnameVARCHAR( -), lnameVARCHAR( -), DOB DATE) PARTITION byRANGE ( Year(DOB)) (PARTITION p0VALUESLess THAN (1970), PARTITION p1VALUESLess THAN (1980), PARTITION p2VALUESLess THAN (1990));

1. Deleting a partition

ALTER TABLE DROP PARTITION P1;

Note: If a partition is deleted, the data in that partition will be lost, not only in the show create table members\g; The command will not see any information about the deleted partition when it views the creation statement for that table.

For a range partition, if the P1 partition is deleted, when the data is inserted, if the date is within the range of 1970 to 1980, the data will be assigned to the next partition, the P2.

For a list partition, if a partition is deleted, insert will error if data belongs to this partition when the data is inserted.

If you are simply deleting data without deleting the partition's information, you can use the TRUNCATE command

ALTER TABLE TRUNCATE PARTITION P1;

2. Adding partitions

ALTER TABLE ADD VALUES Less THAN (+));

Note: Adding a partition using the Add command can only be added at the end of the partition list, specifically in this case, only after 1990.

Of course, in the actual production environment, such limitations are too large, for example, I want to add a partition before the P0 partition, the interval is 1960, or add a 1975 partition between P1, this time, with add will not meet such requirements, you can use alter TABLE ... REORGANIZE partition command.

Such as:

ALTER TABLE  into (    values less THAN (1960),    values less THAN (1970 ));

The reorganize command is actually quite flexible and can be used not only to split partitions but also to merge partitions, such as:

ALTER TABLE  into (    values less THAN (1980),    thevalues less THAN ( ));

Attention:

1> cannot use the reorganize partition command to modify a table's partition type, only through ALTER TABLE ... PARTITION by .... Statements, such as:

ALTER TABLE members      by  Year (DOB))     8;

The 2> REORGANIZE partition syntax is as follows:

ALTER TABLE Tbl_name    REORGANIZE PARTITION partition_list    into (partition_definitions);

The range of partitions in the Partition_definitions must cover the range of partitions in the partition_list.

Second, how to manage the hash and key partition

Take the partition table for example

CREATE TABLE clients (    INT,    varchar()    ,varchar (a) by    MONTH;

For hash partitions and key partitions, the above range and list partitioning syntax, such as the Drop,truncate,reorganize partition, is not supported.

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

ALTER TABLE COALESCE 4;

The purpose of this command is to trim the partition of the Clients table by 4, from 12 to 8.

ALTER TABLE ADD 6;

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

MySQL partition table Management to