创建 RANGE 分区表

RANGE 分区简单的语法格式如下：

CREATE TABLE table_name (
    column_name1        column_type
    [, column_nameN     column_type]
) PARTITION BY RANGE ( expr(column_name1)  | column_name1)
(
    PARTITION   p0      VALUES LESS THAN ( expr )
    [, PARTITION pN     VALUES LESS THAN (expr ) ]
 [, PARTITION pX    VALUES LESS THAN (MAXVALUE) ]
);

在创建 RANGE 分区时，需要遵循以下规则：

• PARTITION BY RANGE ( expr ) 里的expr表达式的结果必须为整型。

• 每个分区都有一个VALUES LESS THAN子句，它为分区指定一个非包含的上限值。分区键的任何值等于或大于这个值时将被映射到下一个分区中。

• 除第一个分区外，所有分区都隐含一个下限值，即上一个分区的上限值。

• 仅允许最后一个分区的上限定义为MAXVALUE，这个值没有具体的数值，并且比其他所有分区的上限都要大，也包含空值。

示例：

• MySQL 模式下，创建一个 RANGE 分区表。

  obclient>CREATE TABLE t_log_part_by_range (
      log_id      bigint NOT NULL 
      , log_value varchar(50)
      , log_date  timestamp NOT NULL  
  ) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date)) 
  (
      PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
      , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
      , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
      , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
      , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
      , PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
      , PARTITION M202007 VALUES LESS THAN(UNIX_TIMESTAMP('2020/08/01'))
      , PARTITION M202008 VALUES LESS THAN(UNIX_TIMESTAMP('2020/09/01'))
      , PARTITION M202009 VALUES LESS THAN(UNIX_TIMESTAMP('2020/10/01'))
      , PARTITION M202010 VALUES LESS THAN(UNIX_TIMESTAMP('2020/11/01'))
      , PARTITION M202011 VALUES LESS THAN(UNIX_TIMESTAMP('2020/12/01'))
      , PARTITION M202012 VALUES LESS THAN(UNIX_TIMESTAMP('2021/01/01'))
  );

• Oracle 模式下，创建一个 RANGE 分区表。

  obclient> CREATE TABLE t_log_part_by_range (
      log_id      number NOT NULL 
      , log_value varchar2(50)
      , log_date  date NOT NULL DEFAULT sysdate
  ) PARTITION BY RANGE(log_date) 
  (
      PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
      , PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
      , PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
      , PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
      , PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
      , PARTITION M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD'))
      , PARTITION M202007 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD'))
      , PARTITION M202008 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD'))
      , PARTITION M202009 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD'))
      , PARTITION M202010 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD'))
      , PARTITION M202011 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD'))
      , PARTITION M202012 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD'))
      , PARTITION MMAX VALUES LESS THAN (MAXVALUE)
  );

RANGE 分区可以新增和删除分区。如果最后一个 RANGE 分区指定了MAXVALUE ，则不能新增分区。

MySQL 模式下，RANGE 分区要求表分区键表达式的结果必须为整型，如果要按时间类型列做 RANGE 分区，则必须使用 Timestamp 类型，并且使用函数 UNIX_TIMESTAMP 将时间类型转换为数值。这个需求也可以使用 RANGE COLUMNS 分区实现，并且不需要表拆分键表达式的结果为整型。

创建 RANGE COLUMNS 分区表

在 OceanBase 数据库中，仅 MySQL 模式支持 RANGE COLUMNS 分区。

RANGE COLUMNS 分区的简单语法格式如下：

CREATE TABLE table_name (
    column_name1        column_type
    [, column_nameN     column_type]
) PARTITION BY RANGE ( column_name1 [, column_name2] )
(
    PARTITION   p0      VALUES LESS THAN ( expr )
    [, PARTITION pN     VALUES LESS THAN (expr ) ]
[, PARTITION pX VALUES LESS THAN (maxvalue) ]
);

示例：创建一个 RANGE COLUMNS 分区。

obclient>CREATE TABLE t_log_part_by_range_columns (
    log_id      bigint NOT NULL 
    , log_value varchar(50)
    , log_date  date NOT NULL  
) PARTITION BY RANGE COLUMNS(log_date) 
(
    PARTITION M202001 VALUES LESS THAN('2020/02/01')
    , PARTITION M202002 VALUES LESS THAN('2020/03/01')
    , PARTITION M202003 VALUES LESS THAN('2020/04/01')
    , PARTITION M202004 VALUES LESS THAN('2020/05/01')
    , PARTITION M202005 VALUES LESS THAN('2020/06/01')
    , PARTITION M202006 VALUES LESS THAN('2020/07/01')
    , PARTITION M202007 VALUES LESS THAN('2020/08/01')
    , PARTITION M202008 VALUES LESS THAN('2020/09/01')
    , PARTITION M202009 VALUES LESS THAN('2020/10/01')
    , PARTITION M202010 VALUES LESS THAN('2020/11/01')
    , PARTITION M202011 VALUES LESS THAN('2020/12/01')
    , PARTITION M202012 VALUES LESS THAN('2021/01/01')
    , PARTITION MMAX VALUES LESS THAN MAXVALUE
);

创建 LIST 分区表

LIST 分区的简单语法格式如下：

CREATE TABLE table_name (
    column_name1        column_type
    [, column_nameN     column_type]
) PARTITION BY LIST ( expr(column_name1)  | column_name1)
(
    PARTITION   p0      VALUES IN ( v01 [, v0N] )
    [, PARTITION pN     VALUES IN ( vN1 [, vNN] ) ]
 [, PARTITION pX    VALUES IN (default) ]
);

当使用 LIST 分区时，需要遵循以下规则：

• 分区表达式的结果必须是整型。

• 分区表达式只能引用一列，不能有多列（即列向量）。

示例：

• MySQL 模式下创建一个 LIST 分区表。

  obclient>CREATE TABLE t_part_by_list ( 
                  c1 BIGINT PRIMARY KEY
                  , c2 VARCHAR(50)
                  ) PARTITION BY list(c1) 
                  ( 
                  PARTITION p0    VALUES IN (1, 2, 3)
                  , PARTITION p1  VALUES IN (5, 6)
                  , PARTITION p2  VALUES IN (DEFAULT)
                  );

• Oracle 模式下创建一个 LIST 分区表。

  obclient> CREATE TABLE t_log_part_by_list (
      log_id      number NOT NULL 
      , log_value varchar2(50)
      , log_date  date NOT NULL DEFAULT sysdate
      , PRIMARY key(log_id, log_value)
  ) PARTITION BY list(log_value) 
  (
      PARTITION P01 VALUES ( '01' )
      , PARTITION P02 VALUES ( '02' )
      , PARTITION P03 VALUES ( '03' )
      , PARTITION P04 VALUES ( '04' )
      , PARTITION P05 VALUES ( '05' )
  );
  Query OK, 0 rows affected (0.10 sec)
  obclient> INSERT INTO t_log_part_by_list(log_id, log_value) values(1,'01'),(2,'02'),(3,'03'),(4,'04'),(5,'05'),(6,'01');
  Query OK, 6 rows affected (0.01 sec)
  Records: 6  Duplicates: 0  Warnings: 0

创建 LIST COLUMNS 分区表

在 OceanBase 数据库中，仅 MySQL 模式支持 LIST COLUMNS 分区。

LIST COLUMNS 分区简单的语法格式如下：

CREATE TABLE table_name (
    column_name1        column_type
    [, column_nameN     column_type]
) PARTITION BY LIST COLUMNS ( column_name1 [, column_nameN ] )
(
    PARTITION   p0      VALUES IN ( v01 [, v0N] )
    [, PARTITION pN     VALUES IN ( vN1 [, vNN] ) ]
 [, PARTITION pX    VALUES IN (default) ]
);

示例：创建一个 LIST COLUMNS 分区表。

obclient>CREATE TABLE t2 (
                id varchar(64),
                type varchar(16),
                info varchar(512),
                gmt_create datetime(6),
                gmt_modified datetime(6),
                partition_id varchar(2) GENERATED ALWAYS AS (substr(`id`,19,20)) VIRTUAL,
                PRIMARY KEY (id)
                ) partition by list columns(partition_id)
                (partition p0 values in ('00','01'),
                partition p1 values in ('02','03'),
                partition p2 values in (default));

创建 HASH 分区表

示例：在 MySQL 模式和 Oracle 模式下，创建一个 HASH 分区表。

obclient>CREATE TABLE ware(
                w_id int
                , w_ytd number(12,2)
                , w_tax number(4,4)
                , w_name varchar(10)
                , w_street_1 varchar(20)
                , w_street_2 varchar(20)
                , w_city varchar(20)
                , w_state char(2)
                , w_zip char(9)
                , primary key(w_id)
                ) PARTITION by hash(w_id) partitions 60;

创建 KEY 分区表

在 OceanBase 数据库中，仅 MySQL 模式支持 KEY 分区。

示例：创建表 t_log_part_by_key，将 id、gmt_create 键作为分区键，按 KEY 分区划分为 3 个分区。

obclient>CREATE TABLE t_log_part_by_key(
  id INT, 
  gmt_create DATETIME, 
  info VARCHAR(20))
  PARTITION BY KEY(id, gmt_create)
  PARTITIONS 3;