How to create a range-range partitioned table - PolarDB - Alibaba Cloud Documentation Center

PolarDB for MySQL supports RANGE-RANGE composite partitioning, where both the top-level partition and each subpartition use RANGE or RANGE COLUMNS as the partitioning strategy. Each row is first assigned to a partition based on the partition key value, then assigned to a subpartition within that partition based on the subpartition key value.

Syntax

CREATE TABLE ... PARTITION BY RANGE {(expr) | COLUMNS(column_list)}
   SUBPARTITION BY RANGE(expr)
[(partition_definition [, partition_definition] ...)];

partition_definition:

PARTITION partition_name
       VALUES LESS THAN {(value | value_list) | MAXVALUE}
[(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:

SUBPARTITION subpartition_name
       VALUES LESS THAN {value | MAXVALUE}

Parameters

Parameter	Description
`expr`	The partition expression. Must be the INT type. String types are not supported.
`column_list`	The list of partition key columns, used with `RANGE COLUMNS()`. Expressions are not supported.
`value`	The boundary value of the partition.
`value_list`	The list of partition key column values, used with `RANGE COLUMNS()`.
`MAXVALUE`	The maximum value of the partition.
`partition_name`	The name of the partition. Must be unique within the table.
`subpartition_name`	The name of the subpartition. Must be unique within the table.

Examples

RANGE-RANGE partitioned table

Partition by dept_no at the top level, then by part_no within each partition:

CREATE TABLE sales_range_range
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE(dept_no)
SUBPARTITION BY RANGE(part_no)
(
  PARTITION p0 VALUES LESS THAN (1000) (
    SUBPARTITION s0 VALUES LESS THAN(100),
    SUBPARTITION s1 VALUES LESS THAN(200),
    SUBPARTITION s2 VALUES LESS THAN(300),
    SUBPARTITION s3 VALUES LESS THAN(MAXVALUE)
  ),
  PARTITION p1 VALUES LESS THAN (2000)
  (
    SUBPARTITION s4 VALUES LESS THAN(100),
    SUBPARTITION s5 VALUES LESS THAN(200),
    SUBPARTITION s6 VALUES LESS THAN(300),
    SUBPARTITION s7 VALUES LESS THAN(MAXVALUE)
  ),
  PARTITION p2 VALUES LESS THAN (MAXVALUE)
  (
    SUBPARTITION s8 VALUES LESS THAN(100),
    SUBPARTITION s9 VALUES LESS THAN(200),
    SUBPARTITION s10 VALUES LESS THAN(300),
    SUBPARTITION s11 VALUES LESS THAN(MAXVALUE)
  )
);

A row with dept_no = 500 and part_no = 150 is assigned to partition p0 (because 500 < 1000), then to subpartition s1 (because 150 falls in the range 100–200).

RANGE COLUMNS-RANGE partitioned table

Partition by a composite key (dept_no, part_no) using RANGE COLUMNS, then subpartition by amount:

CREATE TABLE sales_range_columns_range
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE COLUMNS(dept_no, part_no)
SUBPARTITION BY RANGE(amount)
(
  PARTITION p1 VALUES LESS THAN(1000, MAXVALUE)(
    SUBPARTITION s0 VALUES LESS THAN(100),
    SUBPARTITION s1 VALUES LESS THAN(200),
    SUBPARTITION s2 VALUES LESS THAN(300),
    SUBPARTITION s3 VALUES LESS THAN(MAXVALUE)
  ),
  PARTITION p2 VALUES LESS THAN(2000, MAXVALUE)(
    SUBPARTITION s4 VALUES LESS THAN(100),
    SUBPARTITION s5 VALUES LESS THAN(200),
    SUBPARTITION s6 VALUES LESS THAN(300),
    SUBPARTITION s7 VALUES LESS THAN(MAXVALUE)
  ),
  PARTITION p3 VALUES LESS THAN(MAXVALUE, MAXVALUE)(
    SUBPARTITION s8 VALUES LESS THAN(100),
    SUBPARTITION s9 VALUES LESS THAN(200),
    SUBPARTITION s10 VALUES LESS THAN(300),
    SUBPARTITION s11 VALUES LESS THAN(MAXVALUE)
  )
);

Usage notes

Subpartition names must be unique across the entire table, not just within a single partition.
The expr used in SUBPARTITION BY RANGE(expr) must evaluate to an INT value. String types are not supported.
RANGE COLUMNS() accepts a column list but does not support expressions.