开发指南—DDL语句—分区表语法—CREATE TABLE

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 本文主要介绍使用DDL语句进行建表的语法、子句、参数和基本方式

本文主要介绍使用DDL语句进行建表的语法、子句、参数和基本方式。

语法


CREATE [PARTITION] TABLE [IF NOT EXISTS] tbl_name
    (create_definition, ...)
    [table_options]
    [table_partition_definition]
create_definition:
    col_name column_definition
  | mysql_create_definition
  | [UNIQUE] GLOBAL INDEX index_name [index_type] (index_sharding_col_name,...)
      [global_secondary_index_option]
      [index_option] ...
index_sharding_col_name:
    col_name [(length)] [ASC | DESC]
index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
index_type:
    USING {BTREE | HASH}
# 全局二级索引相关
global_secondary_index_option:
    [COVERING (col_name,...)]
    [partition_options]
# 分区表类型定义
table_partition_definition:
        single
  | broadcast
  | partition_options
# 分区策略定义
partition_options:
    PARTITION BY
          HASH({column_name | partition_func(column_name)})
        | KEY(column_list)
        | RANGE{({column_name | partition_func(column_name)}) 
        | RANGE COLUMNS(column_list)}
        | LIST{({column_name | partition_func(column_name)}) 
        | LIST COLUMNS(column_list)} }
    partition_list_spec
# 分区函数定义
partition_func:
    YEAR
  | TO_DAYS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH
# 分区列表定义
partition_list_spec:
        hash_partition_list
  | range_partition_list
  | list_partition_list
# Hash / Key 分区表列定义
hash_partition_list:
    PARTITIONS partition_count
# Range / Range Columns 分区表列定义
range_partition_list:
    range_partition [, range_partition ...]
range_partition:
    PARTITION partition_name VALUES LESS THAN {(expr | value_list)} [partition_spec_options]
    
# List / List Columns 分区表列定义
list_partition_list:
    list_partition [, list_partition ...]
list_partition:
    PARTITION partition_name VALUES IN (value_list) [partition_spec_options]
partition_spec_options:
          [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string']
        [{CHARSET | CHARACTER SET} [=] charset]
        [COLLATE [=] collation]
        [TABLEGROUP [=] table_group_id]
        [LOCALITY [=] locality_option]
locality_option:
    'dn=storage_inst_id_list'
    
storage_inst_id_list:
    storage_inst_id[,storage_inst_id_list]


说明 PolarDB-X DDL语法基于MySQL语法,以上主要列出了差异部分,详细语法请参见MySQL 文档

默认自动分区

  • 建表SQL在不指定分区键的情况下,PolarDB-X默认会按主键(如果表没有指定主键,则使用隐式主键)并使用KEY分区策略进行分区,默认分区的分区数目是64。
  • 用户也可以通过指定CREATE PARTITION TABLE语法关键字进行建表,来显示让PolarDB-X选择主键进行自动分区,示例如下:
CREATE PARTITION TABLE auto_part_tbl(

id bigint not null auto_increment,
bid int,
name varchar(30),
primary key(id)
);

单表与广播表

PolarDB-X允许创建表时通过指定关键字SINGLE来建单表(不进行任何分区的表),示例如下:


CREATE TABLE single_tbl(
id bigint not null auto_increment,
bid int,
name varchar(30),
primary key(id)
) SINGLE;

PolarDB-X允许创建表时通过指定关键字BROADCAST来建广播表(该表将在所有DN节点上有一份数据完全相同的拷贝),示例如下:


CREATE TABLE broadcast_tbl(
id bigint not null auto_increment,
bid int,
name varchar(30),
primary key(id)
) BROADCAST;

分区表

PolarDB-X允许创建表时通过指定分区子句的语义,来创建符合业务需要求的分区表。从总体上,PolarDB-X支持三种类型的分区策略:

  • Hash分区策略:基于用户指定的分区列或分区函数表达式的值,使用内置的一致性哈希算法计算其哈希值并进行分区路由的策略。按是否支持使用分区函数表达式或使用多个分区列作为分区键,Hash分区策略又可以进行步细分为Hash分区与Key分区两种类型。
  • Range分区策略:基于用户指定的分区列或分区函数表达式的值,通过比较计算来确定其所落在哪些预定义分区的范围并进行分区路由的策略。按是否支持使用分区函数表达式或使用多个分区列作为分区键,Range分区策略又可以进行步细分为Range分区与Range Columns分区两种类型。
  • List分区策略:与Range分区策略类似,基于用户指定的分区列或分区函数表达式的值,通过比较计算来确定其所落在哪些预指定义分区的取值集合并进行分区路由的策略。按是否支持使用分区函数表达式或使用多个分区列作为分区键,List 分区策略又可以进行步细分为List分区与List Columns分区两种类型。

Hash分区策略

PolarDB-X按是否支持使用分区函数表达式或使用多个分区列作为分区键,Hash分区策略进行步细分为Hash分区与Key分区两种类型。

  • Hash分区Hash分区建表只支持使用一个整数类型的分区列作为分区健,但对于时间类型的分区列,它支持这些分区列外层套用一个分区函数表达式(例如,YEAR/TO_DAYS/TO_SECOND/MONTH等)来转换成整数类型。其次需要值得注意的是,Hash分区是不支持直接使用字符串类型作为分区列。如果想要按照用户ID列进行分区,预建的Hash分区数目是8,可以执行以下命令建表:
CREATE TABLE hash_tbl(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
partition by hash(bid)
partitions 8;
  • 如果想要按找用户出生日期birthday列进行分区,预建的Hash分区数目是8,可以执行以下命令建表:
CREATE TABLE hash_tbl_todays(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY HASH(TO_DAYS(birthday))
PARTITIONS 8;
  • 目前,PolarDB-X的分区函数仅支持以下列表:
    • YEAR
    • TO_DAYS
    • TO_SECOND
    • MONTH
    • UNIX_TIMESTAMP
  • Key分区与Hash分区不同,使用Key分区建表,支持同时使用多个分区列作为分区键,但它不允许分区列外层套用任何分区函数表达。Key分区的分区列的类型支持比Hash分区更为丰富,支持的类型如下所示:
    • 整数类型:BIGINT/BIGINT UNSINGEDINT/INT UNSINGED/MEDIUMINT/MEDIUMINT UNSINGED/SMALLINT/SMALLINT UNSINGED/TINYINT/TINYINT UNSINGED
    • 时间类型:DATETIME/DATE/TIMESTAMP
    • 字符串类型:CHAR/VARCHR
  • 因此,Key分区是PolarDB-X的默认分区策略。
    如果想要按照用户ID列与用户出生日期列作为分区键进行分区,预建的Hash分区数目是8,可以执行以下命令建表:
CREATE TABLE key_tbl(
id bigint not null auto_increment,
bid int,
name varchar(30),
birthday datetime not null,
primary key(id)
)
PARTITION BY KEY(id, birthday)
PARTITIONS 8;

Range分区策略

PolarDB-X按照是否支持使用分区函数表达式或使用多个分区列作为分区键,将Range分区策略进一步细分为Range分区与Range Columns分区两种类型。

  • Range分区Range分区只支持使用一个整数类型的分区列作为分区健,但对于时间类型的分区列,它支持这些分区列外层套用一个分区函数表达式(例如,YEAR/TO_DAYS/TO_SECOND/MONTH等)来转换成整数类型。另外值得注意的是,Range分区是不支持直接使用字符串类型作为分区列。

    说明 Range不支持使用NULL值作为边界值。
    如果业务想按订单的日期进行Range分区,并且每个季度一个分区,可以执行以下命令建表:
CREATE TABLE orders(
id int,
order_time datetime not null)
PARTITION BY RANGE(to_days(order_time))
(
PARTITION p1 VALUES LESS THAN (to_days('2021-01-01')),
PARTITION p2 VALUES LESS THAN (to_days('2021-04-01')),
PARTITION p3 VALUES LESS THAN (to_days('2021-07-01')),
PARTITION p4 VALUES LESS THAN (to_days('2021-10-01')),
PARTITION p5 VALUES LESS THAN (to_days('2022-01-01'))
);
  • Range Columns分区Range Columns分区支持同时使用多个分区列作为分区键,但它不允许分区列外层套用任何分区函数表达。Range Columns分区的分区列的类型支持比Range分区更为丰富,支持的类型如下所示:
    • 整数类型:BIGINT/BIGINT UNSINGEDINT/INT UNSINGED/MEDIUMINT/MEDIUMINT UNSINGED/SMALLINT/SMALLINT UNSINGED/TINYINT/TINYINT UNSINGED
    • 时间类型:DATETIME/DATE
    • 字符串类型:CHAR/VARCHR
  • 说明
    • Range Columns分区目前还不支的使用TIMESTAMP类型,后续版本会完善。
    • 不支持使用NULL值作为边界值。
  • 如果业务想要按照订单ID与订单日期进行Range分区, 可以执行以下命令建表:
CREATE TABLE orders(
order_id int,
order_time datetime not null)
PARTITION BY range columns(order_id,order_time)
(
PARTITION p1 VALUES LESS THAN (10000,'2021-01-01'),
PARTITION p2 VALUES LESS THAN (20000,'2021-01-01'),
PARTITION p3 VALUES LESS THAN (30000,'2021-01-01'),
PARTITION p4 VALUES LESS THAN (40000,'2021-01-01'),
PARTITION p5 VALUES LESS THAN (50000,'2021-01-01')
);

List分区策略

与Range分区类似,PolarDB-X按是否支持使用分区函数表达式或使用多个分区列作为分区键,将List分区策略进一步细分为List分区与List Columns分区两种类型。

  • List分区List分区只支持使用一个整数类型的分区列作为分区健,但对于时间类型的分区列,它支持这些分区列外层套用一个分区函数表达式(例如,YEAR/TO_DAYS/TO_SECOND/MONTH等)来转换成整数类型。另外值得注意的是,Range分区是不支持直接使用字符串类型作为分区列。
    如果业务想按订单的日期进行List分区,并且每个季度一个分区,可以执行以下命令建表:
CREATE TABLE orders(
id int,
order_region varchar(64),
order_time datetime not null)
PARTITION BY LIST(YEAR(order_time))
(
PARTITION p1 VALUES IN (1990,1991,1992,1993,1994,1995,1996,1997,1998,1999),
PARTITION p2 VALUES IN (2000,2001,2002,2003,2004,2005,2006,2007,2008,2009),
PARTITION p3 VALUES IN (2010,2011,2012,2013,2014,2015,2016,2017,2018,2019)
);
  • List Columns分区List Columns分区支持同时使用多个分区列作为分区键,但它不允许分区列外层套用任何分区函数表达。List Columns分区的分区列的类型支持比List分区更为丰富,支持的类型如下所示:
    • 整数类型:BIGINT/BIGINT UNSINGEDINT/INT UNSINGED/MEDIUMINT/MEDIUMINT UNSINGED/SMALLINT/SMALLINT UNSINGED/TINYINT/TINYINT UNSINGED
    • 时间类型:DATETIME/DATE
    • 字符串类型:CHAR/VARCHR
  • 说明
    • List Columns分区目前还不支的使用TIMESTAMP类型,后续版本进行完善。
    • 不支持使用NULL值作为边界值。
  • 如果业务按照订单ID与订单日期进行Range分区,可以执行以下命令建表:
CREATE TABLE orders(
id int,
order_region varchar(64),
order_time datetime not null)
PARTITION BY LIST COLUMNS(order_region)
(
PARTITION p1 VALUES IN ('Hangzhou', 'Shenzhen'),
PARTITION p2 VALUES IN ('Beijing', 'Shanghai'),
PARTITION p3 VALUES IN ('Qingdao')
);

分区表数据类型

各种分区策略及其数据类型支持如下:

数据类型 Hash类型(HASH) Hash类型(KEY) Range类型(Range) Range类型(Range Columns) List类型(List) List类型(List Columns)
数值类型 TINYINT image支持 image支持 image支持 image支持 image支持 image支持
TINYINT UNSIGNED image支持 image支持 image支持 image支持 image支持 image支持
SMALLINT image支持 image支持 image支持 image支持 image支持 image支持
SMALLINT UNSIGNED image支持 image支持 image支持 image支持 image支持 image支持
MEDIUMINT image支持 image支持 image支持 image支持 image支持 image支持
MEDIUMINT UNSIGNED image支持 image支持 image支持 image支持 image支持 image支持
INT image支持 image支持 image支持 image支持 image支持 image支持
INT UNSIGNED image支持 image支持 image支持 image支持 image支持 image支持
BIGINT image支持 image支持 image支持 image支持 image支持 image支持
BIGINT UNSIGNED image支持 image支持 image支持 image支持 image支持 image支持
时间类型 DATE image分区列需要套用year/month/to_days/to_seconds函数。 image支持 image分区列需要套用year/month/to_days/to_seconds函数。 image支持 image分区列需要套用year/month/to_days/to_seconds函数。 image支持
DATETIME image分区列需要套用year/month/to_days/to_seconds函数。 image支持 image分区列需要套用year/month/to_days/to_seconds函数。 image支持 image分区列需要套用year/month/to_days/to_seconds函数。 image支持
TIMESTAMP image分区列必须要外套unix_timestamp函数配合使用。 image支持 image暂不支持 image暂不支持 image暂不支持 image暂不支持
字符串类型 CHAR image不支持 image支持 image不支持 image支持 image不支持 image支持
VARCHAR image不支持 image支持 image不支持 image支持 image不支持 image支持

参数说明

参数 说明
CHARSET | CHARACTER SET 指定表中列的默认字符集,可使用字符集如下:
  • utf8
  • utf8mb4
  • gbk
COLLATE 指定表中列的默认字符序,可使用字符序如下:
  • utf8_bin
  • utf8_general_ci
  • utf8_unicode_ci
  • gbk_bin
  • gbk_chinese_ci
  • utf8mb4_general_ci
  • utf8mb4__general_cs
  • utf8mb4_bin
  • utf8mb4_unicode_ci
TABLEGROUP 用于指定分区表所属于的表组。若不指定,会自动查找或创建与之分区方式完全一致的表组。
LOCALITY 用于指定分区表的所在DN节点。
相关实践学习
快速体验PolarDB开源数据库
本实验环境已内置PostgreSQL数据库以及PolarDB开源数据库:PolarDB PostgreSQL版和PolarDB分布式版,支持一键拉起使用,方便各位开发者学习使用。
相关文章
|
17天前
|
SQL Java API
Apache Flink 2.0-preview released
Apache Flink 社区正积极筹备 Flink 2.0 的发布,这是自 Flink 1.0 发布以来的首个重大更新。Flink 2.0 将引入多项激动人心的功能和改进,包括存算分离状态管理、物化表、批作业自适应执行等,同时也包含了一些不兼容的变更。目前提供的预览版旨在让用户提前尝试新功能并收集反馈,但不建议在生产环境中使用。
469 13
Apache Flink 2.0-preview released
|
3月前
|
消息中间件 SQL Kafka
实时计算 Flink版产品使用问题之使用StarRocks作为Lookup Join的表是否合适
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2月前
|
消息中间件 canal 关系型数据库
Maxwell:binlog 解析器,轻松同步 MySQL 数据
Maxwell:binlog 解析器,轻松同步 MySQL 数据
232 11
|
2月前
|
存储 数据采集 OLAP
饿了么基于Flink+Paimon+StarRocks的实时湖仓探索
饿了么的实时数仓经历了多个阶段的演进。初期通过实时ETL、报表应用、联动及监控构建基础架构,随后形成了涵盖数据采集、加工和服务的整体数据架构。1.0版本通过日志和Binlog采集数据,但在研发效率和数据一致性方面存在问题。2.0版本通过Dataphin构建流批一体化系统,提升了数据一致性和研发效率,但仍面临新业务适应性等问题。最终,饿了么选择Paimon和StarRocks作为实时湖仓方案,显著降低了存储成本并提高了系统稳定性。未来,将进一步优化带宽瓶颈、小文件问题及权限控制,实现更多场景的应用。
271 7
饿了么基于Flink+Paimon+StarRocks的实时湖仓探索
|
3月前
|
存储 监控 Oracle
实时计算 Flink版产品使用问题之如何解决双流Join导致的状态膨胀和资源压力问题
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
3月前
|
Oracle 关系型数据库 MySQL
实时计算 Flink版产品使用问题之如何从savepoint重新启动作业
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
6月前
|
SQL 关系型数据库 MySQL
MySQL | 数据库的管理和操作【表的增删改查】(二)
MySQL | 数据库的管理和操作【表的增删改查】(二)
|
SQL 算法 关系型数据库
开发指南—DDL语句—分区表语法—CREATE TABLE
本文主要介绍使用DDL语句进行建表的语法、子句、参数和基本方式
184 0
|
6月前
|
分布式计算 DataWorks Java
DataWorks常见问题之数据集成导出分区表的全量数据如何解决
DataWorks是阿里云提供的一站式大数据开发与管理平台,支持数据集成、数据开发、数据治理等功能;在本汇总中,我们梳理了DataWorks产品在使用过程中经常遇到的问题及解答,以助用户在数据处理和分析工作中提高效率,降低难度。
151 0
|
关系型数据库 MySQL 数据挖掘
寻找最大值:解析MySQL中的MAX()函数
在数据库管理中,寻找最大值是常见的数据分析任务,MAX()函数正是用于帮助我们实现这一目标的工具。
347 0