暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 创建二级分区表

创建二级分区表

SQL新手 2022-12-08
228

二级分区是按照两个维度来把数据拆分成分区的操作。最常用的地方是类似用户账单的场景。

二级分区分区类型

OceanBase 数据库目前 MySQL 模式支持 HASH、RANGE、 LIST、KEY、RANGE COLUMNS 和 LIST COLUMNS 六种分区方式，二级分区为任意两种分区方式的组合。创建二级分区表支持情况详见下表：

二级分区类型创建模板化二级分区表创建非模板化二级分区表
Range + Range支持支持
Range + Range Columns支持支持
Range + List支持支持
Range + List Columns支持支持
Range + Hash支持支持
Range + Key支持支持
Range Columns + Range支持支持
Range Columns + Range Columns支持支持
Range Columns + List支持支持
Range Columns + List Columns支持支持
Range Columns + Hash支持支持
Range Columns + Key支持支持
List + Range支持支持
List + Range Columns支持支持
List + List支持支持
List + List Columns支持支持
List + Hash支持支持
List + Key支持支持
List Columns + Range支持支持
List Columns + Range Columns支持支持
List Columns + List支持支持
List Columns + List Columns支持支持
List Columns + Hash支持支持
List Columns + Key支持支持
Hash + Range支持支持
Hash + Range Columns支持支持
Hash + List支持支持
Hash + List Columns支持支持
Hash + Hash支持支持
Hash + Key支持支持
Key + Range支持支持
Key + Range Columns支持支持
Key + List支持支持
Key + List Columns支持支持
Key + Hash支持支持
Key + Key支持支持

创建二级分区表

二级分区表可分为模板化二级分区表和非模板化二级分区表。

创建模板化二级分区表的 SQL 语法

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

说明

  • 模板化二级分区表的每个一级分区下的二级分区都按照模板中的二级分区定义，即每个一级分区下的二级分区定义均相同。
  • 对于模板化二级分区表来说，二级分区的命名规则为 ($part_name)s($subpart_name)。例如：对于下⾯的 tbl_rr 表，一级分区 p0 下的 3 个二级分区的分区名分别为 p0smp1、p0smp2、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 语法

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]...

参数解释

参数说明
table_name指定表名。
column_name指定列名。
column_type指定列数据类型。
locality_name指定副本在 Zone 间的分布情况。例如：F@z1,F@z2,F@z3,R@z4 表示 z1、 z2、z3 为全功能副本，z4 为只读副本。
primary_zone_name指定主 Zone（副本 Leader 所在 Zone）。
partition_name指定一级分区名称。
subpartition_name指定二级分区名称。
INT_VALUE指定 hash 或 Key 类型的二级分区个数。

示例

创建模板化二级分区表

  • 创建模板化 Range Columns + Range Columns 分区表。

    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

    查看已创建的分区表 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

    插入数据到 t_m_rcrc。

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

    查看已新建的分区表 t_m_rcrc。

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

  • 创建模板化 Range Columns + List Columns 分区表。

    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

    查看已创建的分区表 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)

  • 创建模板化 Range Columns + Hash 分区表。

    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

    查看已创建的分区表 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

    插入数据到 t2_m_rch。

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

    查看已新建的分区表 t2_m_rch。

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

  • 创建模板化 Range Columns + Key 分区表。

    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

    查看已创建的分区表 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

    插入数据到 t2_m_rck。

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

    查看已新建的分区表 t2_m_rck。

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

  • 创建模板化 List Columns + Range Columns 分区表。

    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

    查看已创建的分区表 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

    插入数据到 t2_m_lcrc。

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

    查看已新建的分区表 t2_m_lcrc。

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

  • 创建模板化 List Columns + List Columns 分区表。

    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

    查看已创建的分区表 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

    插入数据到 t2_m_lclc。

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

    查看已新建的分区表 t2_m_lclc。

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

  • 创建模板化 List Columns + Hash 分区表。

    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

    查看已创建的分区表 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)

    插入数据到 t2_m_lch。

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

    查看已新建的分区表 t2_m_lch。

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

  • 创建模板化 List Columns + Key 分区表。

    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

    查看已创建的分区表 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

    插入数据到 t2_m_lck。

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

    查看已新建的分区表 t2_m_lck。

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

  • 创建模板化 Hash + Range分区表。

    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

    查看已创建的分区表 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

    插入数据到 tbl2_m_hr。

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

    查看已新建的分区表 t2_m_hr。

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

  • 创建模板化 Hash + List 分区表。

    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

    查看已创建的分区表 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)

    插入数据到 tbl2_m_hl。

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

    查看已新建的分区表 t2_m_hl。

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

  • 创建模板化 Hash + Hash 分区表。

    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

    查看已创建的分区表 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

    插入数据到 tbl2_m_hh。

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

    查看已新建的分区表 tbl2_m_hh。

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

  • 创建模板化 Hash + Key 分区表。

    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

    查看已创建的分区表 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

    插入数据到 tbl2_m_hk。

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

    查看已新建的分区表 tbl2_m_hk。

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

  • 创建模板化 Key + Range Columns 分区表。

    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

    查看已创建的分区表 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

    插入数据到 tbl2_m_krc。

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

    查看已新建的分区表 tbl2_m_krc。

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

  • 创建模板化 Key + List Columns 分区表。

    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

    查看已创建的分区表 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)

    插入数据到 tbl2_m_klc。

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

    查看已新建的分区表 tbl2_m_klc。

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

  • 创建模板化 Key + Hash 分区表。

    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

    查看已创建的分区表 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)

    插入数据到 tbl2_m_kh。

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

    查看已新建的分区表 tbl2_m_kh。

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

  • 创建模板化 Key + Key 分区表。

    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

    查看已创建的分区表 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)

    插入数据到 tbl2_m_kk。

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

    查看已新建的分区表 tbl2_m_kk。

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

创建非模板化二级分区表

  • 创建非模板化 Range + Range 分区表。

    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

    查看已创建的分区表 tbl2_m_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

    插入数据到 t2_f_rr。

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

    查看已新建的分区表 t2_f_rr。

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

  • 创建非模板化 Range Columns + List Columns 分区表。

    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

    查看已创建的分区表 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)

    插入数据到 t2_f_rclc。

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

    查看已新建的分区表 t2_f_rclc。

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

  • 创建非模板化 Range Columns + Hash 分区表。

    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

    查看已创建的分区表 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

    插入数据到 t2_f_rch。

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

    查看已新建的分区表 t2_f_rch。

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

  • 创建非模板化 Hash + Range 分区表。

    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

    查看已创建的分区表 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

    插入数据到 t2_f_hr。

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

    查看已新建的分区表 t2_f_hr。

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

  • 创建非模板化 Hash + List 分区表。

    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

    查看已创建的分区表 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

    插入数据到 t2_f_hl。

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

    查看已新建的分区表 t2_f_hl。

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

  • 创建非模板化 Hash + Key 分区表。

    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

    查看已创建的分区表 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

    插入数据到 t2_f_hl。

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

    查看已新建的分区表 t2_f_hl。

    obclient> SELECT * FROM t2_f_hl partition(sp6);
+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
+------+------+
1 row in set
分区表数据库分区oceanbase
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论