MySQL list partition

Introduction

The list partition and the range partition are very similar, the main difference being that the list is a collection of enumerated value lists, and range is a continuous set of interval values. The two are very similar in terms of syntax. It is also recommended that the list partition column is a non-null column, otherwise inserting a null value if there is no null value in the enumeration list fails to insert, which is not the same as other partitions, and the range partition stores it as a minimum partition value, which is hash\key into 0 storage. The primary list partition only supports shaping, and non-shaping fields need to be converted to shaping by functions; After version 5.5, you can use the list column partition to support non-shaping fields, which are explained in detail in the column partition.

First, create a partition

The values of the enumeration for each partition of the list need only be different, not in a fixed order.

CREATE TABLEtblist (IDINT  not NULL, store_idINT) PARTITION byLIST (store_id) (PARTITION aVALUES inch(1,5,6), PARTITION bVALUES inch(2,7,8), PARTITION cVALUES inch(3,9,Ten), PARTITION DVALUES inch(4, One, A));

SELECTfromWHERE table_schema=SCHEMA and table_name  ='tblist';

1. Inserting data

Insert  into Values (1,1), (7,7

Insert a record into each of the A and B two partitions

2. Inserting values that are not in the list

Inserting a null value if the value that is not in the enumeration list is inserted into the partition fails, inserting the nulls if the null value is not in the enumeration list also fails

Second, partition management

1. Adding partitions

ALTER TABLE ADD VALUES inch ());

Note: You cannot increase a partition that contains any of the existing values.

2. Merging partitions

ALTER TABLE tblist REORGANIZE PARTITION into the  VALUES in (1,5,  6,2,7,8));

Merge Partition A, b into partition m

Note: As with range partitions, you can merge only several contiguous partitions and not across partitions. For example, you cannot merge a,c two partitions, only by merging A,b,c

3. Splitting partitions

ALTER TABLETblist REORGANIZE PARTITION A,b,c into(PARTITION nVALUES inch(1,5,6,3,9,Ten), PARTITION mVALUES inch(2,7,8));ALTER TABLETblist REORGANIZE PARTITION N into(PARTITION aVALUES inch(1,5,6), PARTITION bVALUES inch(3,9,Ten));

After two rounds of splitting, the enumeration list (3,9,10) went to the front of the (2,7,8); in fact, at first merging ABC into NM two partitions because the enumeration value in N is less than m so N is in front of M, After splitting the n partition because the n partition is in front of the M partition, the split partition is also in front of the M partition, because the value of a partition is less than the value of the B partition, so a is in front of B.

Note: 1. In the 5.7.12 release, the test found that the merged and split partition redefined enumeration value can be not the original value, if the original enumeration value contains data and the newly merged or split partition enumeration value does not contain the original enumeration value will cause data loss. Although it is not known why MySQL does not prohibit this behavior, the artificial requirements for either merging or splitting the partition enumeration values remain the same, or only the increase cannot be reduced, which guarantees that the data is not lost.

2. After merging and splitting the partition because it is adjacent to the partition to merge and split the new partition according to the original partition value will also be in the original partition's order location.

4. Deleting a partition

ALTER TABLE DROP PARTITION e;

Note: Deleting a partition also deletes the data in the partition, and the enumerated list value is also deleted, after which the value's data cannot be inserted into the table.

Third, other partitions

1. Partitioning a Time field

CREATE TABLElistdate (IDINT  not NULL, hiredDATETIME  not NULL) PARTITION byLIST ( Year(hired)) (PARTITION aVALUES inch(1990), PARTITION bVALUES inch(1991), PARTITION cVALUES inch(1992), PARTITION DVALUES inch(1993));ALTER TABLEListdateADD INDEXix_hired (hired);INSERT  intoListdate ()VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(1,'1992-01-01 10:00:00');

SELECT *  from WHERE hired='1990-01-01 10:00:00';

The list partition also supports converting partitions to non-shaping time-type fields.

Iv. removing partitions from a table

ALTER TABLE Tablenameremove Partitioning;

Note: Removing a partition using remove removes only the definition of the partition and does not delete the data and the drop partition, which is deleted along with the data

Reference:

Range Partition: http://www.cnblogs.com/chenmh/p/5627912.html

Column partition: http://www.cnblogs.com/chenmh/p/5630834.html

Hash Partition: Http://www.cnblogs.com/chenmh/p/5644496.html

Key partition: Http://www.cnblogs.com/chenmh/p/5647210.html

Sub-partition: http://www.cnblogs.com/chenmh/p/5649447.html

Specify each partition path: http://www.cnblogs.com/chenmh/p/5644713.html

Partition index: http://www.cnblogs.com/chenmh/p/5761995.html

Partition Introduction Summary: http://www.cnblogs.com/chenmh/p/5623474.html

Summary

When you redefine a list partition, you can only redefine adjacent partitions, you cannot skip the partition definition, the redefined partition list enumeration must contain the list enumeration of the original partition, and if you lose an enumeration value that contains a record, the data will also be deleted; redefining the partition cannot change the type of the partition.

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly give the link. Welcome to the exchange of discussions

MySQL list partition