LIST partitions of mysql partitions

LIST partitions of mysql partitions

LIST partitions in MySQL are similar to RANGE partitions in many aspects. Like partitioning by RANGE, each partition must be clearly defined. The main difference between them is that the definition and selection of each partition in LIST partitions are based on the value of a column from a value in a Value LIST set, the RANGE partition is a set of continuous RANGE values. LIST partitions are implemented BY using "partition by list (expr)", where "expr" is a column value or an expression based on a column value and returns an integer, then, each partition is defined using the "values in (value_list)" method. "value_list" is a list of integers separated by commas.

Note: in MySQL 5.1, when LIST partitions are used, only integer lists can be matched.

Unlike defining partitions by RANGE, LIST partitions do not have to declare any specific order.

For the example given below, we assume that the basic definition of the TABLE to be partitioned is provided through the following "create table" statement:

Create table employees (

Id int not null,

Fname VARCHAR (30 ),

Lname VARCHAR (30 ),

Hired date not null default '2017-01-01 ',

Separated date not null default '2017-12-31 ',

Job_code INT,

Store_id INT

);

(This is the same table as in section 18.2.1, "RANGE partition ).

Assume that there are 20 audio stores distributed in 4 regions with sales permissions, as shown in the following table:

Region store ID

North Area 3, 5, 6, 9, 17

Eastern Region 1, 2, 10, 11, 19, 20

West Area 4, 12, 13, 14, 18

Central Area 7, 8, 15, 16

To split a TABLE by storing rows in the same partition of a store in the same region, use the following "create table" statement:

Create table employees (

Id int not null,

Fname VARCHAR (30 ),

Lname VARCHAR (30 ),

Hired date not null default '2017-01-01 ',

Separated date not null default '2017-12-31 ',

Job_code INT,

Store_id INT

)

Partition by list (store_id)

PARTITION pNorth values in (3,5, 6,9, 17 ),

PARTITION pEast values in ),

PARTITION pWest values in (4, 12, 13, 14, 18 ),

PARTITION pCentral values in (7, 8, 15, 16)

);

This makes it easy to add or delete employee records in the specified region in the table. For example, assume that all audio stores in the west region are sold to other companies. All records (rows) related to employees working in the west region audio and video store can be deleted using the "alter table employees drop partition pWest;" query. It is similar to the DELETE (DELETE) statement) query "DELETE query delete from employees WHERE store_id IN (, 18);" is more effective than that.

Key point: if you try to INSERT a column value (or the return value of the partition expression) not in a row in the partition Value List, the "INSERT" query will fail and report an error. For example, if the LIST partition adopts the above scheme, the following query will fail:

Insert into employees VALUES

(224, 'linus', 'torvalds ', '2017-05-01', '2017-10-12 ', 42, 21 );

This is because the value 21 of the "store_id" Column cannot be found in the list of values used to define partitions pNorth, pEast, pWest, or pCentral. Note that LIST partitions do not have definitions that include other VALUES, such as "values less than maxvalue. Any value to be matched must be found in the Value List.

In addition to combining with RANGE partitions, LIST partitions can generate a composite subpartition. It is also possible to combine with HASH and KEY partitions to generate composite subpartitions.