Introduction to two ways to create partitions in mysql (code examples)

This article brings you an introduction to the two ways to create partitions in MySQL (code examples). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

#list partition--the key value is determined by a custom list to which partition it is written to.

Advantages: Supports int, time, varchar and other values ​​

Disadvantages: You need to write the slave of the corresponding data yourself (which partition is written or queried), that is, if the partition conditions are modified later, it needs to be configured again .

CREATE TABLE t_test (
    unid INT auto_increment , 
    uuid VARCHAR(36),
    cdate datetime,
    type int,
        text varchar(30),
        PRIMARY KEY(unid,type)
)
PARTITION BY LIST COLUMNS(type) (        #这里以type字段来分区，list分区中，这个字段可以为int整形或者某个值
    PARTITION pRegion_1  VALUES IN (1),     #这里的意思是，当type=1时，数据会写入到pRegion_1分区中
    PARTITION pRegion_2  VALUES IN (2),     #同上
    PARTITION pRegion_3  VALUES IN (3),
    PARTITION pRegion_4  VALUES IN (4)
);

#Hash partition--After the key value is calculated through the hash algorithm, it is automatically written to the corresponding partition.

Advantages: You do not need to write the corresponding data slave yourself (which partition to write or query)

Disadvantages: Only int integer type is supported

CREATE TABLE t_test (
    unid INT auto_increment ,
    uuid VARCHAR(36),
    cdate datetime,
    type int,
    text varchar(30),
    PRIMARY KEY(unid,type)       #复合主键，因为后面要用type字段来分区
)
PARTITION BY HASH ( type )   #这里以type字段来分区，type必须是主键或者是复合主键包含的字段，hash分区的方式必须该字段为int
PARTITIONS 10;              #这里设定的是分区数为10，数据会通过type字段经过hash算法后，自动归属到10个分区中的某个分区中

The above is the detailed content of Introduction to two ways to create partitions in mysql (code examples). For more information, please follow other related articles on the PHP Chinese website!