创建一级分区表

本文介绍了一级分区表的创建方法和相关事项。

概述

支持一级分区表的类型包括：Range、Range Columns、List、List Columns、Hash、Key。

创建 Range/Range Columns 分区表

语法

CREATE TABLE table_name (column_name column_type[, column_name column_type])
  PARTITION BY { RANGE (expr(column_name) | column_name) 
                | RANGE COLUMNS(column_name [,column_name])
                }
    (
     PARTITION partition_name VALUES LESS THAN(expr)
     [, PARTITION partition_name VALUES LESS THAN (expr )...]
     [, PARTITION partition_name VALUES LESS THAN (MAXVALUE)]
     );

说明

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

• 在 MySQL 模式中 PARTITION BY RANGE ( expr ) 里的 expr 表达式的结果必须为整型。如果要按时间类型列做 Range 分区，则必须使用 Timestamp 类型，并且使用函数 UNIX_TIMESTAMP 将时间类型转换为数值。这个需求也可以使用 Range Columns 分区实现，并且不需要表拆分键表达式的结果为整型。
• 每个分区都有一个 VALUES LESS THAN 子句，它为分区指定一个非包含的上限值。分区键的任何值等于或大于这个值时将被映射到下一个分区中。
• 除第一个分区外，所有分区都隐含一个下限值，即上一个分区的上限值。
• 仅允许最后一个分区的上限定义为 MAXVALUE，这个值没有具体的数值，并且比其他所有分区的上限都要大，也包含空值。如果最后一个 Range 分区指定了 MAXVALUE ，则不能新增分区。

参数解释

示例

• 创建一个 Range 分区表 tbl1_r。

  obclient> CREATE TABLE tbl1_r (log_id BIGINT NOT NULL,log_value VARCHAR(50),log_date TIMESTAMP NOT NULL)
         PARTITION BY RANGE(UNIX_TIMESTAMP(log_date)) 
          (PARTITION M197001 VALUES LESS THAN(UNIX_TIMESTAMP('1970/02/01'))
         , PARTITION M197002 VALUES LESS THAN(UNIX_TIMESTAMP('1970/03/01'))
         , PARTITION M197003 VALUES LESS THAN(UNIX_TIMESTAMP('1970/04/01'))
         , PARTITION M197004 VALUES LESS THAN(UNIX_TIMESTAMP('1970/05/01'))
         , PARTITION M197005 VALUES LESS THAN(UNIX_TIMESTAMP('1970/06/01'))
         , PARTITION M197006 VALUES LESS THAN(UNIX_TIMESTAMP('1970/07/01'))
         , PARTITION M197007 VALUES LESS THAN(UNIX_TIMESTAMP('1970/08/01'))
         , PARTITION M197008 VALUES LESS THAN(UNIX_TIMESTAMP('1970/09/01'))
         , PARTITION M197009 VALUES LESS THAN(UNIX_TIMESTAMP('1970/10/01'))
         , PARTITION M197010 VALUES LESS THAN(UNIX_TIMESTAMP('1970/11/01'))
         , PARTITION M197011 VALUES LESS THAN(UNIX_TIMESTAMP('1970/12/01'))
         , PARTITION M197012 VALUES LESS THAN(UNIX_TIMESTAMP('1971/01/01'))
          );
  Query OK, 0 rows affected
  

  查看已新建的分区表 tbl1_r。

  obclient>show create table tbl1_r\G
  *************************** 1. row ***************************
         Table: tbl1_r
  Create Table: CREATE TABLE `tbl1_r` (
    `log_id` bigint(20) NOT NULL,
    `log_value` varchar(50) DEFAULT NULL,
    `log_date` timestamp NOT 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(UNIX_TIMESTAMP(log_date))
  (partition M197001 values less than (1580486400),
  partition M197002 values less than (1582992000),
  partition M197003 values less than (1585670400),
  partition M197004 values less than (1588262400),
  partition M197005 values less than (1590940800),
  partition M197006 values less than (1593532800),
  partition M197007 values less than (1596211200),
  partition M197008 values less than (1598889600),
  partition M197009 values less than (1601481600),
  partition M197010 values less than (1604160000),
  partition M197011 values less than (1606752000),
  partition M197012 values less than (1609430400))
  1 row in set
  

  向 tbl1_r 表插入数据。

  obclient> INSERT INTO tbl1_r VALUES (10,'10','1970/04/01 00:00:00.000000');
  Query OK, 1 row affected
  

  查看数据落入的分区。

  obclient> SELECT * FROM tbl1_r partition(M197004);
  +--------+-----------+---------------------+
  | log_id | log_value | log_date            |
  +--------+-----------+---------------------+
  |     10 | 10        | 1970-04-01 00:00:00 |
  +--------+-----------+---------------------+
  1 row in set
  

• 创建一个 Range Columns 分区表 tbl1_log_rc。

  obclient> CREATE TABLE tbl1_log_rc (log_id BIGINT NOT NULL,log_value VARCHAR(50),log_date DATE NOT NULL)
         PARTITION BY RANGE COLUMNS(log_date) 
          (PARTITION M197001 VALUES LESS THAN('1970/02/01')
         , PARTITION M197002 VALUES LESS THAN('1970/03/01')
         , PARTITION M197003 VALUES LESS THAN('1970/04/01')
         , PARTITION M197004 VALUES LESS THAN('1970/05/01')
         , PARTITION M197005 VALUES LESS THAN('1970/06/01')
         , PARTITION M197006 VALUES LESS THAN('1970/07/01')
         , PARTITION M197007 VALUES LESS THAN('1970/08/01')
         , PARTITION M197008 VALUES LESS THAN('1970/09/01')
         , PARTITION M197009 VALUES LESS THAN('1970/10/01')
         , PARTITION M197010 VALUES LESS THAN('1970/11/01')
         , PARTITION M197011 VALUES LESS THAN('1970/12/01')
         , PARTITION M197012 VALUES LESS THAN('1971/01/01')
         , PARTITION MMAX VALUES LESS THAN MAXVALUE
          );
  Query OK, 0 rows affected
  

  查看已新建的分区表 tbl1_log_rc。

  obclient> show create table tbl1_log_rc\G
  *************************** 1. row ***************************
         Table: tbl1_log_rc
  Create Table: CREATE TABLE `tbl1_log_rc` (
    `log_id` bigint(20) NOT NULL,
    `log_value` varchar(50) DEFAULT NULL,
    `log_date` date NOT 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(log_date)
  (partition M197001 values less than ('1970-02-01'),
  partition M197002 values less than ('1970-03-01'),
  partition M197003 values less than ('1970-04-01'),
  partition M197004 values less than ('1970-05-01'),
  partition M197005 values less than ('1970-06-01'),
  partition M197006 values less than ('1970-07-01'),
  partition M197007 values less than ('1970-08-01'),
  partition M197008 values less than ('1970-09-01'),
  partition M197009 values less than ('1970-10-01'),
  partition M197010 values less than ('1970-11-01'),
  partition M197011 values less than ('1970-12-01'),
  partition M197012 values less than ('1971-01-01'),
  partition MMAX values less than (MAXVALUE))
  1 row in set
  

  向 tbl1_log_rc 表插入数据。

  obclient> INSERT INTO tbl1_log_rc VALUES (10,'10','1970/04/01 00:00:00.000000');
  Query OK, 1 row affected
  

  查看数据落入的分区。

  obclient> SELECT * FROM tbl1_log_rc partition(M197004);
  +--------+-----------+---------------------+
  | log_id | log_value | log_date            |
  +--------+-----------+---------------------+
  |     10 | 10        | 1970-04-01 00:00:00 |
  +--------+-----------+---------------------+
  1 row in set
  

创建 List/List Columns 分区表

语法

CREATE TABLE table_name (column_name column_type[,column_name column_type])
  PARTITION BY { LIST ( expr(column_name) | column_name )
                | LIST COLUMNS ( column_name [,column_name])
                }
    (PARTITION partition_name VALUES IN ( v01 [, v0N])
     [,PARTITION partition_name VALUES IN ( vN1 [, vNN])]
     [,PARTITION partition_name VALUES IN (DEFAULT)]
    );

注意

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

• 分区表达式的结果必须是整型。
• 分区表达式只能引用一列，不能有多列（即列向量）。

参数解释

示例

• 创建一个 List 分区表 tbl1_l。

  obclient> CREATE TABLE tbl1_l (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50))
         PARTITION BY LIST(col1) 
          (PARTITION p0 VALUES IN (1, 2, 3),
           PARTITION p1 VALUES IN (5, 6),
           PARTITION p2 VALUES IN (DEFAULT)
          );
  Query OK, 0 rows affected
  

  查看已新建的分区表 tbl1_l。

  obclient> show create table tbl1_l\G
  *************************** 1. row ***************************
         Table: tbl1_l
  Create Table: CREATE TABLE `tbl1_l` (
    `col1` bigint(20) NOT NULL,
    `col2` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`col1`)
  ) 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(col1)
  (partition p0 values in (1,2,3),
  partition p1 values in (5,6),
  partition p2 values in (DEFAULT))
  1 row in set
  

  向 tbl1_l 表插入数据。

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

  查看数据落入的分区。

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

• 创建一个 List Columns 分区表。

  obclient> CREATE TABLE tbl1_lc (id INT,partition_id VARCHAR(2))
         PARTITION BY LIST COLUMNS(partition_id)
          (PARTITION p0 VALUES IN ('00','01'),
           PARTITION p1 VALUES IN ('02','03'),
           PARTITION p2 VALUES IN (DEFAULT)
          );
  Query OK, 0 rows affected
  

  查看已新建的分区表 tbl1_lc。

  obclient> show create table tbl1_lc\G
  *************************** 1. row ***************************
         Table: tbl1_lc
  Create Table: CREATE TABLE `tbl1_lc` (
    `id` int(11) DEFAULT NULL,
    `partition_id` varchar(2) 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(partition_id)
  (partition p0 values in ('00','01'),
  partition p1 values in ('02','03'),
  partition p2 values in (DEFAULT))
  1 row in set 
  

  向 tbl1_lc 表插入数据。

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

  查看数据落入的分区。

  obclient> SELECT * FROM tbl1_lc partition(p2);
  +------+--------------+
  | id   | partition_id |
  +------+--------------+
  |    1 | 1            |
  +------+--------------+
  1 row in set
  

创建 Hash/Key 分区表

语法

CREATE TABLE table_name (column_name column_type[,column_name column_type])
  PARTITION BY { HASH(expr) | KEY([column_name][,column_name])} 
    PARTITIONS partition_count;

参数解释

示例

• 创建一个 Hash 分区表 tbl1_h。

  obclient> CREATE TABLE tbl1_h(col1 INT,col2 VARCHAR(50))
         PARTITION BY HASH(col1) PARTITIONS 60;
  Query OK, 0 rows affected
  

  查看已新建的分区表 tbl1_h。

  obclient> show create table tbl1_h\G
  *************************** 1. row ***************************
         Table: tbl1_h
  Create Table: CREATE TABLE `tbl1_h` (
    `col1` int(11) DEFAULT NULL,
    `col2` varchar(50) 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)
  (partition p0,
  partition p1,
  partition p2,
  partition p3,
  partition p4,
  partition p5,
  partition p6,
  partition p7,
  partition p8,
  partition p9,
  partition p10,
  partition p11,
  partition p12,
  partition p13,
  partition p14,
  partition p15,
  partition p16,
  partition p17,
  partition p18,
  partition p19,
  partition p20,
  partition p21,
  partition p22,
  partition p23,
  partition p24,
  partition p25,
  partition p26,
  partition p27,
  partition p28,
  partition p29,
  partition p30,
  partition p31,
  partition p32,
  partition p33,
  partition p34,
  partition p35,
  partition p36,
  partition p37,
  partition p38,
  partition p39,
  partition p40,
  partition p41,
  partition p42,
  partition p43,
  partition p44,
  partition p45,
  partition p46,
  partition p47,
  partition p48,
  partition p49,
  partition p50,
  partition p51,
  partition p52,
  partition p53,
  partition p54,
  partition p55,
  partition p56,
  partition p57,
  partition p58,
  partition p59)
  1 row in set 
  

  向 tbl1_h 表插入数据。

  obclient> INSERT INTO tbl1_h VALUES (59,'1');
  Query OK, 1 row affected
  

  查看数据落入的分区。

  obclient> SELECT * FROM tbl1_h partition(p59);
  +------+------+
  | col1 | col2 |
  +------+------+
  |   59 | 1    |
  +------+------+
  1 row in set
  

• 创建一个 Key 分区表 tbl1_k。

  obclient> CREATE TABLE tbl1_k(id INT,gmt_create DATETIME,info VARCHAR(20))
         PARTITION BY KEY(id,gmt_create) PARTITIONS 10;
  Query OK, 0 rows affected
  

  查看已新建的分区表 tbl1_k。

  obclient> show create table tbl1_k\G
  *************************** 1. row ***************************
         Table: tbl1_k
  Create Table: CREATE TABLE `tbl1_k` (
    `id` int(11) DEFAULT NULL,
    `gmt_create` datetime DEFAULT NULL,
    `info` varchar(20) 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(id,gmt_create)
  (partition p0,
  partition p1,
  partition p2,
  partition p3,
  partition p4,
  partition p5,
  partition p6,
  partition p7,
  partition p8,
  partition p9)
  1 row in set 
  

  向 tbl1_k 表插入数据。

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

  查看数据落入的分区。

  obclient> SELECT * FROM tbl1_k partition(p9);
  +------+---------------------+------+
  | id   | gmt_create          | info |
  +------+---------------------+------+
  |    1 | 1971-04-01 00:00:00 | 1    |
  +------+---------------------+------+
  1 row in set
  

后续操作

创建一级分区后，您可以对一级分区进行管理，例如：添加一级分区、删除一级分区、Truncate 一级分区等。分区管理的详细操作，请参见 管理一级分区表。