MySQL LIST partition

LIST partition and RANGE partition are very similar. The main difference is that LIST is a collection of enumeration value lists, and RANGE is a collection of continuous interval values. The two are very similar in syntax. It is also recommended that the LIST partition column be a non-null column. Otherwise, inserting a null value will fail if there is no null value in the enumeration list. This is different from other partitions. The RANGE partition will store it as the minimum partition value. HASHKEY will be divided into Convert it to 0 storage. The main LIST partition only supports integers, and non-integers need to be converted into integers through functions. After version 5.5, you can use LIST COLUMN partitions to support non-integers without function conversion. There is a detailed explanation in COLUMN partitions.

1. Create partitions

The enumerated values ​​of each partition in List only need to be different, and there is no fixed order.

CREATE TABLE tblist (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION a VALUES IN (1,5,6),
    PARTITION b VALUES IN (2,7,8),
    PARTITION c VALUES IN (3,9,10),
    PARTITION d VALUES IN (4,11,12)
);

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION 
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblist';

1. Insert data

insert into tblist(id,store_id) values(1,1),(7,7);

Insert a record into each of the two partitions a and b

2. Insert a value that is not in the list

When inserting a record into the partition that is not in the list The value in the enumeration list will fail to be inserted. Inserting a null value will also fail if the null value is not in the enumeration list. 2. Partition management

1. Add a partition

ALTER TABLE tblist ADD PARTITION (PARTITION e VALUES IN (20));

Note: You cannot add any existing value. Partition.

2. Merge partitions

ALTER TABLE tblist REORGANIZE PARTITION  a,b INTO (PARTITION m VALUES IN (1,5,6,2,7,8));

Merge partitions a and b into partition m

Note: Like the RANGE partition, only several adjacent partitions can be merged, and cross-partitions cannot be merged. For example, you cannot merge two partitions a and c. You can only merge a, b, c

3. Split the partition

ALTER TABLE tblist REORGANIZE PARTITION  a,b,c INTO 
(PARTITION n VALUES IN (1,5,6,3,9,10),
PARTITION m VALUES IN (2,7,8));

ALTER TABLE tblist REORGANIZE PARTITION  n INTO 
    ( PARTITION a VALUES IN (1,5,6),
    PARTITION b VALUES IN (3,9,10));

After two rounds of splitting, the list is enumerated (3,9, 10) It is ranked in front of (2,7,8); in fact, it is like this. Initially, abc is merged into two partitions of nm. Since the enumeration value in n is less than m, n is in front of m, and then split later. Since n partition is in front of m partition, the split partition is also ranked in front of m partition. Since the value of a partition is smaller than the value of b partition, a is ranked in front of b.

Note: 1. Testing in version 5.7.12 found that the enumeration value redefined by merging and splitting partitions may not be the original value. If the original enumeration value contains data and the newly merged or split partition enumeration If the enumeration value does not contain the original enumeration value, data will be lost. Although I don't know why mysql does not prohibit this behavior, it is artificially required that the enumeration value of the partition remains unchanged whether it is merging or splitting, or it can only increase but not decrease, so as to ensure that the data is not lost.

2. Since the merged and split partitions are adjacent partitions, the new partition will be merged and split according to the value of the original partition, and the new partition will also be in the order of the original partition.

4. Delete partition

ALTER TABLE tblist DROP PARTITION e;

Note: Deleting a partition will also delete the data in the partition, and the enumerated list value will also be deleted. Data of this value cannot be inserted into the table later.

3. Other partitions

Partition the time field

1. CREATE TABLE listdate (
       id INT NOT NULL,
       hired DATETIME NOT NULL
   )
   PARTITION BY LIST( YEAR(hired) ) 
   (
       PARTITION a VALUES IN (1990),
       PARTITION b VALUES IN (1991),
       PARTITION c VALUES IN (1992),
       PARTITION d VALUES IN (1993)
   );
   
   ALTER TABLE listdate ADD INDEX ix_hired(hired);
   
   INSERT INTO listdate() VALUES(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),(1,'1992-01-01 10:00:00');

   EXPLAIN SELECT * FROM listdate WHERE hired='1990-01-01 10:00:00';

LIST partition also supports conversion partitioning of non-shaped time type fields.

4. Remove table partitions

ALTER TABLE tablename
REMOVE PARTITIONING ;

Note: Using remove to remove a partition only removes the definition of the partition and does not delete the data. This is different from drop PARTITION, which will delete the data together