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

创建一级分区表

SQL新手 2022-12-08
254

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

概述

支持一级分区表的类型包括：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 ，则不能新增分区。

参数解释

参数描述
table_name指定表名。
column_name指定列名称。
column_type指定列的数据类型。
partition_name指定分区名称。

示例

  • 创建一个 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 分区时，需要遵循以下规则：

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

参数解释

参数描述
table_name指定表名。
column_name指定列名称。
column_type指定列的数据类型。
partition_name指定分区名称。
DEFAULT当其值在其它分区没有被匹配时，就会作为默认值，落入该分区。

示例

  • 创建一个 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;

参数解释

参数描述
table_name指定表名。
column_name指定列名。
column_type指定列的数据类型。
expr指定 Hash 分区表达式。
column_name_list指定 Key 分区列名称列表。
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 一级分区等。分区管理的详细操作，请参见 管理一级分区表。

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

评论