Create a subpartitioned table|V4.0.0| docs|Distributed Database

Subpartitioning is a technique that partitions a table in two dimensions. For example, it is frequently used in scenarios that involve bills.

Subpartitioning types

OceanBase Database in MySQL mode supports the HASH, RANGE, LIST, KEY, RANGE COLUMNS, and LIST COLUMNS partitioning types, and a combination of any two of them can be used for subpartitioning. The following table describes the support for subpartitioned table creation.

Subpartitioning type	Create a subpartitioned table by using a template	Create a subpartitioned table without using a template
RANGE-RANGE	Yes	Yes
RANGE-RANGE COLUMNS	Yes	Yes
RANGE-LIST	Yes	Yes
RANGE-LIST COLUMNS	Yes	Yes
RANGE-HASH	Yes	Yes
RANGE-KEY	Yes	Yes
RANGE COLUMNS-RANGE	Yes	Yes
RANGE COLUMNS-RANGE COLUMNS	Yes	Yes
RANGE COLUMNS-LIST	Yes	Yes
RANGE COLUMNS-LIST COLUMNS	Yes	Yes
RANGE COLUMNS-HASH	Yes	Yes
RANGE COLUMNS-KEY	Yes	Yes
LIST-RANGE	Yes	Yes
LIST-RANGE COLUMNS	Yes	Yes
LIST-LIST	Yes	Yes
LIST-LIST COLUMNS	Yes	Yes
LIST-HASH	Yes	Yes
LIST-KEY	Yes	Yes
LIST COLUMNS-RANGE	Yes	Yes
LIST COLUMNS-RANGE COLUMNS	Yes	Yes
LIST COLUMNS-LIST	Yes	Yes
LIST COLUMNS-LIST COLUMNS	Yes	Yes
LIST COLUMNS-HASH	Yes	Yes
LIST COLUMNS-KEY	Yes	Yes
HASH-RANGE	Yes	Yes
HASH-RANGE COLUMNS	Yes	Yes
HASH-LIST	Yes	Yes
HASH-LIST COLUMNS	Yes	Yes
HASH-HASH	Yes	Yes
HASH-KEY	Yes	Yes
KEY-RANGE	Yes	Yes
KEY-RANGE COLUMNS	Yes	Yes
KEY-LIST	Yes	Yes
KEY-LIST COLUMNS	Yes	Yes
KEY-HASH	Yes	Yes
KEY-KEY	Yes	Yes

Create a subpartitioned table

When you create a subpartitioned table, you can choose whether to use a template.

SQL syntax to create a subpartitioned table by using a template

CREATE TABLE [IF NOT EXISTS] table_name(column_option_list)
  [table_option_list] [partition_option_list];

column_option_list:
  column_name column_type [, column_name column_type]

table_option_list:
table_option [table_option]

table_option:
      LOCALITY [=] locality_name
    | PRIMARY_ZONE [=] primary_zone_name

partition_option_list:
PARTITION BY
  RANGE {(expression) | COLUMNS (column_name_list)}{subpartition_option} (range_partition_list)
 | LIST {(expression) | COLUMNS (column_name_list)}{subpartition_option} (list_partition_list)
 | HASH(expression) {subpartition_option} { (hash_partition_list)
                                           | PARTITIONS partition_count }
 | KEY(column_name_list) {subpartition_option} { (key_partition_list)
                                                | PARTITIONS partition_count }

subpartition_option:
  SUBPARTITION BY
     RANGE {(expression) | COLUMNS (column_name_list)} SUBPARTITION TEMPLATE (range_subpartition_list)
    | LIST {(expression) | COLUMNS (column_name_list)} SUBPARTITION TEMPLATE (list_subpartition_list)
    | HASH(expression) { SUBPARTITION TEMPLATE (hash_subpartition_list)
                       | SUBPARTITIONS subpartition_count}
    | KEY(column_name_list) { SUBPARTITION TEMPLATE (key_subpartition_list)
                            | SUBPARTITIONS subpartition_count}

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name VALUES LESS THAN {(expression_list) | MAXVALUE}

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name VALUES LESS THAN {(expression_list) | MAXVALUE}

list_partition_list:
    list_partition [, list_partition ...]

list_partition:
    PARTITION partition_name VALUES IN {(expression_list) | DEFAULT}

list_subpartition_list:
    list_subpartition [, list_subpartition ...]

list_subpartition:
    SUBPARTITION subpartition_name VALUES IN {(expression_list) | DEFAULT}

hash_partition_list:
    hash_partition [, hash_partition ...]

key_partition_list:
    key_partition [, key_partition ...]

hash_partition | key_partition:
    PARTITION partition_name

hash_subpartition_list:
    hash_subpartition [, hash_subpartition ...]

key_subpartition_list:
    key_subpartition [, key_subpartition ...]

hash_subpartition | key_subpartition:
    SUBPARTITION subpartition_name

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_count | subpartition_count:
    INT_VALUE

Note

When you use a template to create a subpartitioned table, the template defines subpartitions under each partition. In this case, subpartitions under each partition share the same definition.

For a template-based subpartitioned table, the subpartitions are named in the format of ($part_name)s($subpart_name). For the tbl_rr table in the following example, the p0 partition has three subpartitions: p0smp1, p0smp2, and p0smp3.

obclient> CREATE TABLE tbl_rr(col1 INT,col2 INT,col3 INT)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION mp1 VALUES LESS THAN(100),
         SUBPARTITION mp2 VALUES LESS THAN(200),
         SUBPARTITION mp3 VALUES LESS THAN(300)
        )
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200),
         PARTITION p2 VALUES LESS THAN(300)
        );
Query OK, 0 rows affected

SQL syntax to create a subpartitioned table without using a template

CREATE TABLE [IF NOT EXISTS] table_name(column_option_list)
  [table_option_list] [partition_option_list];

column_option_list:
  column_name column_type [, column_name column_type]

table_option_list:
table_option [table_option]

table_option:
      LOCALITY [=] locality_name
    | PRIMARY_ZONE [=] primary_zone_name

partition_option_list:
PARTITION BY
  RANGE {(expression) | COLUMNS (column_name_list)}{subpartition_option}
    { range_partition_option (subpartition_option_list)
       [, range_partition_option (subpartition_option_list)]...
     }
 | LIST {(expression) | COLUMNS (column_name_list)}{subpartition_option}
     { list_partition_option (subpartition_option_list)
       [, list_partition_option (subpartition_option_list)]...
      }
 | HASH(expression) {subpartition_option}
    { hash_partition_option (subpartition_option_list)
     [, hash_partition_option (subpartition_option_list)]...
     }
 | KEY(column_name_list) {subpartition_option}
     { key_partition_option (subpartition_option_list)
       [, key_partition_option (subpartition_option_list)]
      }

subpartition_option:
SUBPARTITION BY
  RANGE {(expression) | COLUMNS (column_name_list)}
 | LIST {(expression) | COLUMNS (column_name_list)}
 | HASH (expression)
 | KEY(column_name_list)

subpartition_option_list:
    range_partition_option_list
  | list_partition_option_list
  | hash_partition_option_list
  | key_partition_option_list

range_partition_option_list:
  range_partition_option [, range_partition_option ]...

list_partition_option_list:
  list_partition_option [, list_partition_option ]...

hash_partition_option_list:
  hash_partition_option [, hash_partition_option ]...

key_partition_option_list:
  key_partition_option [, key_partition_option ]...

range_partition_option:
  SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr
    [,SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr]...

list_partition_option:
  SUBPARTITION subpartition_name VALUES IN list_partition_expr
    [, SUBPARTITION subpartition_name VALUES IN list_partition_expr]...

hash_partition_option_list:
  SUBPARTITION subpartition_name
    [, SUBPARTITION subpartition_name]...

key_partition_option_list:
  SUBPARTITION subpartition_name
    [, SUBPARTITION subpartition_name]...

Parameters

Parameter	Description
table_name	The table name.
column_name	The column name.
column_type	The data type of the column.
locality_name	The distribution of replicas across the zones. For example, `F@z1,F@z2,F@z3,R@z4` indicates that `z1`, `z2`, and `z3` are full-featured replicas, and `z4` is a read-only replica.
primary_zone_name	The primary zone where the leader resides.
partition_name	The partition name.
subpartition_name	The subpartition name.
INT_VALUE	The number of HASH or KEY subpartitions.

Examples

Create a subpartitioned table by using a template

Create a RANGE COLUMNS-RANGE COLUMNS-subpartitioned table by using a template.

obclient> CREATE TABLE t_m_rcrc(col1 INT,col2 INT)
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY RANGE COLUMNS(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES LESS THAN(1000),
         SUBPARTITION mp1 VALUES LESS THAN(2000),
         SUBPARTITION mp2 VALUES LESS THAN(3000)
        )
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200),
         PARTITION p2 VALUES LESS THAN(300)
        );
Query OK, 0 rows affected

View the created subpartitioned table t_m_rcrc.

obclient> show create table t_m_rcrc\G
*************************** 1. row ***************************
       Table: t_m_rcrc
Create Table: CREATE TABLE `t_m_rcrc` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by range columns(col1) subpartition by range columns(col2) subpartition template (
subpartition mp0 values less than (1000),
subpartition mp1 values less than (2000),
subpartition mp2 values less than (3000))
(partition p0 values less than (100),
partition p1 values less than (200),
partition p2 values less than (300))
1 row in set

Insert data into the t_m_rcrc table.

obclient> INSERT INTO t_m_rcrc VALUES (1,1818);
Query OK, 1 row affected

View the t_m_rcrc subpartitioned table.

obclient> SELECT * FROM t_m_rcrc partition(p0);
+------+------+
| col1 | col2 |
+------+------+
|    1 | 1818 |
+------+------+
1 row in set

Create a RANGE COLUMNS-LIST COLUMNS-subpartitioned table by using a template.

obclient> CREATE TABLE t_m_rclc(col1 INT,col2 INT)
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES IN(1,3),
         SUBPARTITION mp1 VALUES IN(4,6),
         SUBPARTITION mp2 VALUES IN(7)
        )
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200),
         PARTITION p2 VALUES LESS THAN(300)
        );
Query OK, 0 rows affected

View the created subpartitioned table t_m_rclc.

obclient> show create table t_m_rclc\G
*************************** 1. row ***************************
       Table: t_m_rclc
Create Table: CREATE TABLE `t_m_rclc` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by range columns(col1) subpartition by list columns(col2) subpartition template (
subpartition mp0 values in (1,3),
subpartition mp1 values in (4,6),
subpartition mp2 values in (7))
(partition p0 values less than (100),
partition p1 values less than (200),
partition p2 values less than (300))
1 row in set (0.00 sec)

Create a RANGE COLUMNS-HASH-subpartitioned table by using a template.

obclient> CREATE TABLE t2_m_rch(col1 INT,col2 INT)
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY HASH(col2) SUBPARTITIONS 5
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200),
         PARTITION p2 VALUES LESS THAN(300)
        );
Query OK, 0 rows affected

View the created subpartitioned table t_m_rclc.

obclient> show create table t2_m_rch\G
*************************** 1. row ***************************
       Table: t2_m_rch
Create Table: CREATE TABLE `t2_m_rch` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by range columns(col1) subpartition by hash(col2) subpartition template (
subpartition p0,
subpartition p1,
subpartition p2,
subpartition p3,
subpartition p4)
(partition p0 values less than (100),
partition p1 values less than (200),
partition p2 values less than (300))
1 row in set

Insert data into the t2_m_rch table.

obclient> INSERT INTO t2_m_rch VALUES (1,1818);
Query OK, 1 row affected

View the t2_m_rch subpartitioned table.

obclient> SELECT * FROM t2_m_rch partition(p0);
+------+------+
| col1 | col2 |
+------+------+
|    1 | 1818 |
+------+------+
1 row in set

Create a RANGE COLUMNS-KEY-subpartitioned table by using a template.

obclient> CREATE TABLE t2_m_rck(col1 INT,col2 INT)
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY KEY(col2) SUBPARTITIONS 3
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200),
         PARTITION p2 VALUES LESS THAN(300)
        );
Query OK, 0 rows affected

View the created subpartitioned table t2_m_rck.

obclient> show create table t2_m_rck\G
*************************** 1. row ***************************
       Table: t2_m_rck
Create Table: CREATE TABLE `t2_m_rck` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by range columns(col1) subpartition by key(col2) subpartition template (
subpartition p0,
subpartition p1,
subpartition p2)
(partition p0 values less than (100),
partition p1 values less than (200),
partition p2 values less than (300))
1 row in set

Insert data into the t2_m_rck table.

obclient> INSERT INTO t2_m_rck VALUES (1,1818);
Query OK, 1 row affected

View the t2_m_rck subpartitioned table.

obclient> SELECT * FROM t2_m_rck partition(p0);
+------+------+
| col1 | col2 |
+------+------+
|    1 | 1818 |
+------+------+
1 row in set

Create a LIST COLUMNS-RANGE COLUMNS-subpartitioned table by using a template.

obclient> CREATE TABLE t2_m_lcrc(col1 INT,col2 INT)
       PARTITION BY LIST COLUMNS(col1)
       SUBPARTITION BY RANGE COLUMNS(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES LESS THAN(100),
         SUBPARTITION mp1 VALUES LESS THAN(200),
         SUBPARTITION mp2 VALUES LESS THAN(300)
        )
        (PARTITION p0 VALUES IN(1,3),
         PARTITION p1 VALUES IN(4,6),
         PARTITION p2 VALUES IN(7,9)
        );
Query OK, 0 rows affected

View the created subpartitioned table t2_m_lcrc.

obclient> show create table t2_m_lcrc\G
*************************** 1. row ***************************
       Table: t2_m_lcrc
Create Table: CREATE TABLE `t2_m_lcrc` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by list columns(col1) subpartition by range columns(col2) subpartition template (
subpartition mp0 values less than (100),
subpartition mp1 values less than (200),
subpartition mp2 values less than (300))
(partition p0 values in (1,3),
partition p1 values in (4,6),
partition p2 values in (7,9))
1 row in set

Insert data into the t2_m_lcrc table.

obclient> INSERT INTO t2_m_lcrc VALUES (1,1);
Query OK, 1 row affected

View the t2_m_lcrc subpartitioned table.

obclient> SELECT * FROM t2_m_lcrc partition(p0);
+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
+------+------+
1 row in set

Create a LIST COLUMNS-LIST COLUMNS-subpartitioned table by using a template.

obclient> CREATE TABLE t2_m_lclc(col1 INT,col2 INT)
       PARTITION BY LIST COLUMNS(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES IN(2),
         SUBPARTITION mp1 VALUES IN(5),
         SUBPARTITION mp2 VALUES IN(8)
        )
        (PARTITION p0 VALUES IN(1,3),
         PARTITION p1 VALUES IN(4,6),
         PARTITION p2 VALUES IN(7,9)
        );
Query OK, 0 rows affected

View the created subpartitioned table t2_m_lclc.

obclient> show create table t2_m_lclc\G
*************************** 1. row ***************************
       Table: t2_m_lclc
Create Table: CREATE TABLE `t2_m_lclc` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by list columns(col1) subpartition by list columns(col2) subpartition template (
subpartition mp0 values in (2),
subpartition mp1 values in (5),
subpartition mp2 values in (8))
(partition p0 values in (1,3),
partition p1 values in (4,6),
partition p2 values in (7,9))
1 row in set

Insert data into the t2_m_lclc table.

obclient>  INSERT INTO t2_m_lclc VALUES (1,8);
Query OK, 1 row affected

View the t2_m_lclc subpartitioned table.

obclient> SELECT * FROM t2_m_lclc partition(p0);
+------+------+
| col1 | col2 |
+------+------+
|    1 |    8 |
+------+------+
1 row in set

Create a LIST COLUMNS-HASH-subpartitioned table by using a template.

obclient> CREATE TABLE t2_m_lch(col1 INT,col2 INT)
       PARTITION BY LIST COLUMNS(col1)
       SUBPARTITION BY HASH(col2) SUBPARTITIONS 5
        (PARTITION p0 VALUES IN(100),
         PARTITION p1 VALUES IN(200),
         PARTITION p2 VALUES IN(300)
        );
Query OK, 0 rows affected

View the created subpartitioned table t2_m_lch.

obclient> show create table t2_m_lch\G
*************************** 1. row ***************************
       Table: t2_m_lch
Create Table: CREATE TABLE `t2_m_lch` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by list columns(col1) subpartition by hash(col2) subpartition template (
subpartition p0,
subpartition p1,
subpartition p2,
subpartition p3,
subpartition p4)
(partition p0 values in (100),
partition p1 values in (200),
partition p2 values in (300))
1 row in set (0.00 sec)

Insert data into the t2_m_lch table.

obclient> INSERT INTO t2_m_lch VALUES (100,8);
Query OK, 1 row affected

View the t2_m_lch subpartitioned table.

obclient>  SELECT * FROM t2_m_lch partition(p0);
+------+------+
| col1 | col2 |
+------+------+
|  100 |    8 |
+------+------+
1 row in set

Create a LIST COLUMNS-KEY-subpartitioned table by using a template.

obclient> CREATE TABLE t2_m_lck(col1 INT,col2 INT)
       PARTITION BY LIST COLUMNS(col1)
       SUBPARTITION BY KEY(col2)
        SUBPARTITIONS 3
        (PARTITION p0 VALUES IN(100),
         PARTITION p1 VALUES IN(200),
         PARTITION p2 VALUES IN(300)
        );
Query OK, 0 rows affected

View the created subpartitioned table t2_m_lck.

obclient> show create table t2_m_lck\G
*************************** 1. row ***************************
       Table: t2_m_lck
Create Table: CREATE TABLE `t2_m_lck` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by list columns(col1) subpartition by key(col2) subpartition template (
subpartition p0,
subpartition p1,
subpartition p2)
(partition p0 values in (100),
partition p1 values in (200),
partition p2 values in (300))
1 row in set

Insert data into the t2_m_lck table.

obclient> INSERT INTO t2_m_lck VALUES (100,8);
Query OK, 1 row affected

View the t2_m_lck subpartitioned table.

obclient>  SELECT * FROM t2_m_lck partition(p0);
+------+------+
| col1 | col2 |
+------+------+
|  100 |    8 |
+------+------+
1 row in set

Create a HASH-RANGE-subpartitioned table by using a template.

obclient> CREATE TABLE tbl2_m_hr(col1 INT,col2 INT,col3 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY RANGE(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION p0 VALUES LESS THAN(100),
         SUBPARTITION p1 VALUES LESS THAN(200),
         SUBPARTITION p2 VALUES LESS THAN(300)
         )
        PARTITIONS 5;
Query OK, 0 rows affected

View the created subpartitioned table tbl2_m_hr.

obclient> show create table tbl2_m_hr\G
*************************** 1. row ***************************
       Table: tbl2_m_hr
Create Table: CREATE TABLE `tbl2_m_hr` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by hash(col1) subpartition by range(col2) subpartition template (
subpartition p0 values less than (100),
subpartition p1 values less than (200),
subpartition p2 values less than (300))
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4)
1 row in set

Insert data into the tbl2_m_hr table.

obclient> INSERT INTO tbl2_m_hr VALUES (100,18,188);
Query OK, 1 row affected

View the t2_m_hr subpartitioned table.

obclient> SELECT * FROM tbl2_m_hr partition(p0);
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|  100 |   18 |  188 |
+------+------+------+
1 row in set

Create a HASH-LIST-subpartitioned table by using a template.

obclient> CREATE TABLE tbl2_m_hl(col1 INT,col2 INT,col3 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY LIST(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION p0 VALUES IN(1,3),
         SUBPARTITION p1 VALUES IN(4,6),
         SUBPARTITION p2 VALUES IN(7,9)
         )
        PARTITIONS 5;
Query OK, 0 rows affected

View the created subpartitioned table tbl2_m_hl.

obclient>  show create table tbl2_m_hl\G;
*************************** 1. row ***************************
       Table: tbl2_m_hl
Create Table: CREATE TABLE `tbl2_m_hl` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by hash(col1) subpartition by list(col2) subpartition template (
subpartition p0 values in (1,3),
subpartition p1 values in (4,6),
subpartition p2 values in (7,9))
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4)
1 row in set (0.00 sec)

Insert data into the tbl2_m_hl subpartitioned table.

obclient> INSERT INTO tbl2_m_hr VALUES (1,1,188);
Query OK, 1 row affected

View the t2_m_hl subpartitioned table.

obclient> SELECT * FROM tbl2_m_hl partition(p1);
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 |    1 |  188 |
+------+------+------+
1 row in set

Create a HASH-HASH-subpartitioned table by using a template.

obclient> CREATE TABLE tbl2_m_hh(col1 INT,col2 INT,col3 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY HASH(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION sp0,
         SUBPARTITION sp1,
         SUBPARTITION sp2
         )
        PARTITIONS 5;
Query OK, 0 rows affected

View the created subpartitioned table tbl2_m_hh.

obclient> show create table tbl2_m_hh\G
*************************** 1. row ***************************
       Table: tbl2_m_hh
Create Table: CREATE TABLE `tbl2_m_hh` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by hash(col1) subpartition by hash(col2) subpartition template (
subpartition p0,
subpartition p1,
subpartition p2)
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4)
1 row in set

Insert data into the tbl2_m_hh table.

obclient> INSERT INTO tbl2_m_hh VALUES (1,1,188);
Query OK, 1 row affected

View the tbl2_m_hh subpartitioned table.

obclient> SELECT * FROM tbl2_m_hh partition(p1);
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 |    1 |  188 |
+------+------+------+
1 row in set

Create a HASH-KEY-subpartitioned table by using a template.

obclient> CREATE TABLE tbl2_m_hk(col1 INT,col2 INT,col3 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY KEY(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION sp0,
         SUBPARTITION sp1,
         SUBPARTITION sp2
         )
        PARTITIONS 5;
Query OK, 0 rows affected

View the created subpartitioned table tbl2_m_hk.

obclient> show create table tbl2_m_hk\G
*************************** 1. row ***************************
       Table: tbl2_m_hk
Create Table: CREATE TABLE `tbl2_m_hk` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by hash(col1) subpartition by key(col2) subpartition template (
subpartition p0,
subpartition p1,
subpartition p2)
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4)
1 row in set

Insert data into the tbl2_m_hk table.

obclient> INSERT INTO tbl2_m_hk VALUES (1,1,188);
Query OK, 1 row affected

View the tbl2_m_hk subpartitioned table.

obclient> SELECT * FROM tbl2_m_hk partition(p1);
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 |    1 |  188 |
+------+------+------+
1 row in set

Create a KEY-RANGE COLUMNS-subpartitioned table by using a template.

obclient> CREATE TABLE tbl2_m_krc(col1 INT,col2 INT,col3 INT)
       PARTITION BY KEY(col1)
       SUBPARTITION BY RANGE COLUMNS(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION sp0 VALUES LESS THAN(100),
         SUBPARTITION sp1 VALUES LESS THAN(200),
         SUBPARTITION sp2 VALUES LESS THAN(300)
         )
        (PARTITION p0,
         PARTITION p1,
         PARTITION p2
         );
Query OK, 0 rows affected

View the created subpartitioned table tbl2_m_krc.

obclient>  show create table tbl2_m_krc\G
*************************** 1. row ***************************
       Table: tbl2_m_krc
Create Table: CREATE TABLE `tbl2_m_krc` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by key(col1) subpartition by range columns(col2) subpartition template (
subpartition sp0 values less than (100),
subpartition sp1 values less than (200),
subpartition sp2 values less than (300))
(partition p0,
partition p1,
partition p2)
1 row in set

Insert data into the tbl2_m_krc table.

obclient> INSERT INTO tbl2_m_krc VALUES (100,100,188);
Query OK, 1 row affected

View the tbl2_m_krc subpartitioned table.

obclient> SELECT * FROM tbl2_m_krc partition(p0);
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|  100 |  100 |  188 |
+------+------+------+
1 row in set

Create a KEY-LIST COLUMNS-subpartitioned table by using a template.

obclient> CREATE TABLE tbl2_m_klc(col1 INT,col2 INT,col3 INT)
       PARTITION BY KEY(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION p0 VALUES IN(1,3),
         SUBPARTITION p1 VALUES IN(4,6),
         SUBPARTITION p2 VALUES IN(7,9)
        )
       PARTITIONS 5;
Query OK, 0 rows affected

View the created subpartitioned table tbl2_m_klc.

obclient> show create table tbl2_m_klc\G
*************************** 1. row ***************************
       Table: tbl2_m_klc
Create Table: CREATE TABLE `tbl2_m_klc` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by key(col1) subpartition by list columns(col2) subpartition template (
subpartition p0 values in (1,3),
subpartition p1 values in (4,6),
subpartition p2 values in (7,9))
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4)
1 row in set (0.00 sec)

Insert data into the tbl2_m_klc table.

obclient> INSERT INTO tbl2_m_klc VALUES (1,1,188);
Query OK, 1 row affected

View the tbl2_m_klc subpartitioned table.

obclient> SELECT * FROM tbl2_m_klc partition(p1);
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 |    1 |  188 |
+------+------+------+
1 row in set

Create a KEY-HASH-subpartitioned table by using a template.

obclient> CREATE TABLE tbl2_m_kh(col1 INT,col2 INT,col3 INT)
       PARTITION BY KEY(col1)
       SUBPARTITION BY HASH(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION sp0,
         SUBPARTITION sp1,
         SUBPARTITION sp2
         )
        PARTITIONS 5;
Query OK, 0 rows affected

View the created subpartitioned table tbl2_m_kh.

obclient> show create table tbl2_m_kh\G
*************************** 1. row ***************************
       Table: tbl2_m_kh
Create Table: CREATE TABLE `tbl2_m_kh` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by key(col1) subpartition by hash(col2) subpartition template (
subpartition p0,
subpartition p1,
subpartition p2)
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4)
1 row in set (0.00 sec)

Insert data into the tbl2_m_kh table.

obclient> INSERT INTO tbl2_m_kh VALUES (1,1,188);
Query OK, 1 row affected

View the tbl2_m_kh subpartitioned table.

obclient> SELECT * FROM tbl2_m_kh partition(p1);
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 |    1 |  188 |
+------+------+------+
1 row in set

Create a KEY-KEY-subpartitioned table by using a template.

obclient> CREATE TABLE tbl2_m_kk(col1 INT,col2 INT,col3 INT)
       PARTITION BY KEY(col1)
       SUBPARTITION BY KEY(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION sp0,
         SUBPARTITION sp1,
         SUBPARTITION sp2
         )
        PARTITIONS 5;
Query OK, 0 rows affected

View the created subpartitioned table tbl2_m_kk.

obclient> show create table tbl2_m_kk\G
*************************** 1. row ***************************
       Table: tbl2_m_kk
Create Table: CREATE TABLE `tbl2_m_kk` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by key(col1) subpartition by key(col2) subpartition template (
subpartition p0,
subpartition p1,
subpartition p2)
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4)
1 row in set (0.00 sec)

Insert data into the tbl2_m_kk table.

obclient> INSERT INTO tbl2_m_kk VALUES (1,1,188);
Query OK, 1 row affected

View the tbl2_m_kk subpartitioned table.

obclient> SELECT * FROM tbl2_m_kk partition(p1);
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 |    1 |  188 |
+------+------+------+
1 row in set

Create a subpartitioned table without using a template

Create a RANGE-RANGE-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_rr(col1 INT,col2 TIMESTAMP)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(UNIX_TIMESTAMP(col2))
        (PARTITION p0 VALUES LESS THAN(100)
           (SUBPARTITION sp0 VALUES LESS THAN(UNIX_TIMESTAMP('1971/04/01')),
            SUBPARTITION sp1 VALUES LESS THAN(UNIX_TIMESTAMP('1971/07/01')),
            SUBPARTITION sp2 VALUES LESS THAN(UNIX_TIMESTAMP('1971/10/01')),
            SUBPARTITION sp3 VALUES LESS THAN(UNIX_TIMESTAMP('1972/01/01'))
           ),
         PARTITION p1 VALUES LESS THAN(200)
           (SUBPARTITION sp4 VALUES LESS THAN(UNIX_TIMESTAMP('1971/04/01')),
            SUBPARTITION sp5 VALUES LESS THAN(UNIX_TIMESTAMP('1971/07/01')),
            SUBPARTITION sp6 VALUES LESS THAN(UNIX_TIMESTAMP('1971/10/01')),
            SUBPARTITION sp7 VALUES LESS THAN(UNIX_TIMESTAMP('1972/01/01'))
            )
         );
Query OK, 0 rows affected

View the created subpartitioned table t2_f_rr.

obclient> show create table tbl2_f_rr\G
*************************** 1. row ***************************
       Table: t2_f_rr
Create Table: CREATE TABLE `t2_f_rr` (
  `col1` int(11) DEFAULT NULL,
  `col2` timestamp NULL DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by range(col1) subpartition by range(UNIX_TIMESTAMP(col2))
(partition p0 values less than (100) (
subpartition sp0 values less than (1617206400),
subpartition sp1 values less than (1625068800),
subpartition sp2 values less than (1633017600),
subpartition sp3 values less than (1640966400)),
partition p1 values less than (200) (
subpartition sp4 values less than (1617206400),
subpartition sp5 values less than (1625068800),
subpartition sp6 values less than (1633017600),
subpartition sp7 values less than (1640966400)))
1 row in set

Insert data into the t2_f_rr table.

obclient> INSERT INTO t2_f_rr VALUES (1,'1971/04/01');
Query OK, 1 row affected

View the t2_f_rr subpartitioned table.

obclient> SELECT * FROM t2_f_rr partition(sp1);
+------+---------------------+
| col1 | col2                |
+------+---------------------+
|    1 | 1971-04-01 00:00:00 |
+------+---------------------+
1 row in set

Create a RANGE COLUMNS-LIST COLUMNS-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_rclc (col1 INT,col2 INT)
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0 VALUES IN(1,3),
          SUBPARTITION sp1 VALUES IN(4,6),
          SUBPARTITION sp2 VALUES IN(7,9)),
        PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp3 VALUES IN(1,3),
          SUBPARTITION sp4 VALUES IN(4,6),
          SUBPARTITION sp5 VALUES IN(7,9))
       );
Query OK, 0 rows affected

View the created subpartitioned table t2_f_rclc.

obclient> show CREATE TABLE t2_f_rclc\G
*************************** 1. row ***************************
       Table: t2_f_rclc
Create Table: CREATE TABLE `t2_f_rclc` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by range columns(col1) subpartition by list columns(col2)
(partition p0 values less than (100) (
subpartition sp0 values in (1,3),
subpartition sp1 values in (4,6),
subpartition sp2 values in (7,9)),
partition p1 values less than (200) (
subpartition sp3 values in (1,3),
subpartition sp4 values in (4,6),
subpartition sp5 values in (7,9)))
1 row in set (0.00 sec)

Insert data into the t2_f_rclc table.

obclient> INSERT INTO t2_f_rclc VALUES (1,1);
Query OK, 1 row affected

View the t2_f_rclc subpartitioned table.

obclient> SELECT * FROM t2_f_rclc partition(sp0);
+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
+------+------+
1 row in set

Create a RANGE COLUMNS-HASH-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_rch (col1 INT,col2 INT)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY HASH(col2)
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0,
          SUBPARTITION sp1,
          SUBPARTITION sp2),
        PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp3,
          SUBPARTITION sp4,
          SUBPARTITION sp5)
       );
Query OK, 0 rows affected

View the created subpartitioned table t2_f_rch.

obclient> show CREATE TABLE t2_f_rch\G
*************************** 1. row ***************************
       Table: t2_f_rch
Create Table: CREATE TABLE `t2_f_rch` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by range(col1) subpartition by hash(col2)
(partition p0 values less than (100) (
subpartition sp0,
subpartition sp1,
subpartition sp2),
partition p1 values less than (200) (
subpartition sp3,
subpartition sp4,
subpartition sp5))
1 row in set

Insert data into the t2_f_rch table.

obclient> INSERT INTO t2_f_rch VALUES (1,1);
Query OK, 1 row affected

View the t2_f_rch subpartitioned table.

obclient> SELECT * FROM t2_f_rch partition(sp1);
+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
+------+------+
1 row in set

Create a HASH-RANGE-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_hr (col1 INT,col2 INT)
       PARTITION BY KEY(col1)
       SUBPARTITION BY RANGE(col2)
         (PARTITION p1
          (SUBPARTITION sp0 VALUES LESS THAN (1970)
          ,SUBPARTITION sp1 VALUES LESS THAN (1971)
          ,SUBPARTITION sp2 VALUES LESS THAN (1972)
          ,SUBPARTITION sp3 VALUES LESS THAN (1973)
          ),
          PARTITION p2
          (SUBPARTITION sp4 VALUES LESS THAN (1970)
          ,SUBPARTITION sp5 VALUES LESS THAN (1971)
          ,SUBPARTITION sp6 VALUES LESS THAN (1972)
          ,SUBPARTITION sp7 VALUES LESS THAN (1973)
          )
         );
Query OK, 0 rows affected

View the created subpartitioned table t2_f_hr.

obclient> show CREATE TABLE t2_f_hr\G
*************************** 1. row ***************************
       Table: t2_f_hr
Create Table: CREATE TABLE `t2_f_hr` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by key(col1) subpartition by range(col2)
(partition p1 (
subpartition sp0 values less than (1970),
subpartition sp1 values less than (1971),
subpartition sp2 values less than (1972),
subpartition sp3 values less than (1973)),
partition p2 (
subpartition sp4 values less than (1970),
subpartition sp5 values less than (1971),
subpartition sp6 values less than (1972),
subpartition sp7 values less than (1973)))
1 row in set

Insert data into the t2_f_hr table.

obclient> INSERT INTO t2_f_hr VALUES (1970,1);
Query OK, 1 row affected

View the t2_f_hr subpartitioned table.

obclient> SELECT * FROM t2_f_hr partition(sp0);
+------+------+
| col1 | col2 |
+------+------+
| 1970 |    1 |
+------+------+
1 row in set

Create a HASH-LIST-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_hl (col1 INT,col2 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY LIST(col2)
        (PARTITION p1
          (SUBPARTITION sp0 VALUES IN (1970)
          ,SUBPARTITION sp1 VALUES IN (1971)
          ,SUBPARTITION sp2 VALUES IN (1972)
          ,SUBPARTITION sp3 VALUES IN (1973)
           ),
         PARTITION p2
          (SUBPARTITION sp4 VALUES IN (1970)
          ,SUBPARTITION sp5 VALUES IN (1971)
          ,SUBPARTITION sp6 VALUES IN (1972)
          ,SUBPARTITION sp7 VALUES IN (1973)
           )
        );
Query OK, 0 rows affected

View the created subpartitioned table t2_f_hl.

obclient> show CREATE TABLE t2_f_hl \G
*************************** 1. row ***************************
       Table: t2_f_hl
Create Table: CREATE TABLE `t2_f_hl` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by hash(col1) subpartition by list(col2)
(partition p1 (
subpartition sp0 values in (1970),
subpartition sp1 values in (1971),
subpartition sp2 values in (1972),
subpartition sp3 values in (1973)),
partition p2 (
subpartition sp4 values in (1970),
subpartition sp5 values in (1971),
subpartition sp6 values in (1972),
subpartition sp7 values in (1973)))
1 row in set

Insert data into the t2_f_hl table.

obclient> INSERT INTO t2_f_hl VALUES (1970,1);
Query OK, 1 row affected

View the t2_f_hl subpartitioned table.

obclient> SELECT * FROM t2_f_hl partition(sp0);
+------+------+
| col1 | col2 |
+------+------+
| 1970 | 1970 |
+------+------+
1 row in set

Create a HASH-KEY-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_hk (col1 INT,col2 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY KEY(col2)
        (PARTITION p1
         (SUBPARTITION sp0
         ,SUBPARTITION sp1
         ,SUBPARTITION sp2
         ,SUBPARTITION sp3
         ),
         PARTITION p2
         (SUBPARTITION sp4
         ,SUBPARTITION sp5
         ,SUBPARTITION sp6
         ,SUBPARTITION sp7
         )
        );
Query OK, 0 rows affected

View the created subpartitioned table t2_f_hk.

obclient> show CREATE TABLE t2_f_hk \G
*************************** 1. row ***************************
       Table: t2_f_hk
Create Table: CREATE TABLE `t2_f_hk` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by hash(col1) subpartition by key(col2)
(partition p1 (
subpartition sp0,
subpartition sp1,
subpartition sp2,
subpartition sp3),
partition p2 (
subpartition sp4,
subpartition sp5,
subpartition sp6,
subpartition sp7))
1 row in set

Insert data into the t2_f_hl table.

obclient> INSERT INTO t2_f_hk VALUES (1,1);
Query OK, 1 row affected

View the t2_f_hl subpartitioned table.

obclient> SELECT * FROM t2_f_hl partition(sp6);
+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
+------+------+
1 row in set