手动创建分区表（AUTO模式）_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

本文介绍了手动创建分区表、选择分区和查看分区信息的语法。

注意事项

使用分区表语法之前，请务必确认创建当前的逻辑库时模式指定为自动分区模式（mode='auto' ），非自动分区模式不允许使用分区表的建表语法。您也可以通过SHOW CREATE DATBASE db_name语法查看当前逻辑库的建表模式。示例如下：

CREATE DATABASE part_db mode='auto';
Query OK, 1 row affected (4.29 sec)

SHOW CREATE DATABASE part_db;
+----------+-----------------------------------------------+
| DATABASE | CREATE DATABASE                               |
+----------+-----------------------------------------------+
| part_db  | CREATE DATABASE `part_db` /* MODE = 'auto' */ |
+----------+-----------------------------------------------+
1 row in set (0.18 sec)

创建数据库的语法详情，请参见CREATE DATABASE。

如果分区表的主键不含分区键，且不是自增主键，需要业务保证主键的唯一性。
若创建表时要使用二级分区的相关功能，实例版本必须为5.4.17-16952556及以上。

分区类型介绍

一级分区

PolarDB-X的一级分区支持四大类型7种分区策略（Hash/Key/RangeColumns/Range/List/List Columns）：

Hash类型：基于用户指定的分区列或分区函数表达式的值，使用内置的一致性哈希算法计算其哈希值并进行分区路由的策略。按哈希路由算法以及使用方式的不同，Hash类型分为Key分区与Hash分区两种分区策略。
Range类型：基于用户指定的分区列或分区函数表达式的值，通过比较计算来确定其所落在哪些预定义分区的范围并进行分区路由的策略。按是否多个分区列作为分区键以及其使用方式的不同，Range类型分为Range Columns分区与Range分区两种分区策略。
List类型：与Range分区策略类似，基于用户指定的分区列或分区函数表达式的值，通过比较计算来确定其所落在哪些预定义分区的取值集合并进行分区路由的策略。按是否多个分区列作为分区键以及其使用方式的不同，List类型也分为List Columns分区与List分区两种分区策略。
CoHash类型。PolarDB-X还针对比较常见的特定的应用场景新扩展了一种新的名为CoHash的哈希分区策略，该策略可有效解决一个表需要同时按多个不同的相互有协同关系的分区列进行水平分区的问题。

更多详细内容请参见分区类型介绍。

二级分区

PolarDB-X的二级分区支持Hash/Key/RangeColumns/Range/List/List Columns共6种分区策略。因此，PolarDB-X的一级分区与二级分区是完全正交的关系，二级分区支持使用任意两种分区策略进行组合，组合分区的数目支持达36种。

二级分区除了支持36种组合分区外，任意一种组合分区均支持使用模板化定义与非模板化定义两种用法。

模板化与非模板化

模板化二级分区：各个一级分区之下的二级分区的分区数目及其分区边界值始终一致；
非模板化二级分区：各个一级分区之下的二级分的分区数目及其分区边界值允许不一致。

重要

二级分区除了支持36种的组合分区外，任意一种组合分区均支持使用模板化定义与非模板化定义两种用法。

使用二级分区的注意点

分区表默认所有一级分区的二级分区数目之和不允许超过8192；
使用二级分区后，分区表的二级分区数目是所有的一级分区下的二级分区数目之和。因此，分区表的分区数将呈倍级上升。因此，请谨慎控制一级分区与二级分区的各自的分片数目，避免过度分区产生副作用，或超出分区总数限制而报错。

创建一级分区

创建HASH一级分区

使用分区函数

使用单列分区键datetime并通过分区函数to_days转换为日期天数，再进行哈希分区，同时指定分区数目为8：

CREATE TABLE tb_h_fn(
 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;

不用分区函数

使用单列分区键ID进行哈希分区，并指定分区数目为8：

CREATE TABLE tb_h(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY HASH(id) 
PARTITIONS 8;

使用向量分区键（id，bid）进行哈希分区，并指定分区数目为8：

说明

id与bid同时参与哈希值计算，等值查询必须同时带上id与bid等值条件，才能命中分区裁剪。

CREATE TABLE tb_h2(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY HASH(id，bid) 
PARTITIONS 8;

创建KEY一级分区

使用向量分区键（id，bid）按Key分区，并指定分区数目为8：

说明

點认第1个列参与哈希值计算，等值查询只须同时前缀列等值条件，即可命中分区裁剪。

CREATE TABLE tb_k(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(id, bid) 
PARTITIONS 8;

创建RANGE一级分区

使用分区函数

使用单列分区键datetime并通过分区函数to_days转换为日期天数，再进行范围分区：

CREATE TABLE tb_r_fn(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday))
(
   PARTITION p1 VALUES LESS THAN(TO_DAYS('2020-01-01')),
   PARTITION p2 VALUES LESS THAN(TO_DAYS('2021-01-01')),
   PARTITION p3 VALUES LESS THAN(TO_DAYS('2022-01-01')),
   PARTITION pm VALUES LESS THAN(MAXVALUE)
)

不用分区函数

使用单列分区键id进行范围分区，并指定分区数目为8：

CREATE TABLE tb_r(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(id)
(
   PARTITION p1 VALUES LESS THAN(1000),
   PARTITION p2 VALUES LESS THAN(2000),
   PARTITION p3 VALUES LESS THAN(3000),
   PARTITION pm VALUES LESS THAN(MAXVALUE)
)

创建RANGE COLUMNS一级分区

使用向量分区键（birthday，id）按范围分区：

CREATE TABLE tb_rc(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE COLUMNS(birthday, id)
(
   PARTITION p1 VALUES LESS THAN('2020-01-01', 1000),
   PARTITION p2 VALUES LESS THAN('2021-01-01', 2000),
   PARTITION p3 VALUES LESS THAN('2022-01-01', 3000),
   PARTITION pm VALUES LESS THAN(MAXVALUE,MAXVALUE)
)

创建LIST一级分区

使用分区函数

使用单列分区键datetime并通过分区函数to_days转换为日期天数，再进行列表分区：

CREATE TABLE tb_l_fn(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday))
(
   PARTITION p1 VALUES IN (TO_DAYS('2020-01-01'),TO_DAYS('2020-02-01')),
   PARTITION p2 VALUES IN (TO_DAYS('2021-01-01'),TO_DAYS('2021-02-01')),
   PARTITION p3 VALUES IN (TO_DAYS('2022-01-01')),
   PARTITION pm VALUES IN (DEFAULT)
)

不用分区函数

使用单列分区键id进行列表分区，并指定分区数目为8：

CREATE TABLE tb_l(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(id)
(
   PARTITION p1 VALUES IN (1000,1001,1002),
   PARTITION p2 VALUES IN (2000,2001,2002),
   PARTITION p3 VALUES IN (3000),
   PARTITION pm VALUES IN (DEFAULT)
)

创建LIST COLUMNS一级分区

使用向量分区键（birthday，id）按列表分区：

CREATE TABLE tb_lc(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday, id)
(
   PARTITION p1 VALUES IN (('2020-01-01', 1000),('2020-01-01', 2000)),
   PARTITION p2 VALUES IN (('2021-01-01', 1000),('2021-01-01', 2000)),
   PARTITION p3 VALUES IN (('2022-01-01', 1000),('2022-01-01', 2000)),
   PARTITION pm VALUES IN (DEFAULT)
)

创建CO_HASH一级分区

当表中存在两个列或多个列的值总是能保持协同（比如，常见的订单表的order_id与buyer_id的后N位数字总是相同的），那么使用CO_HASH分区策略进行分区，能够达到查询单独带上任意一个分区列的等值条件，都可以有效命中分区裁剪并被路由到一个物理分区的效果。

CO_HASH(a,b)与HASH(a,b)、KEY()a,b)的主要区别

不同点1: 多个分区列之间的关系不同

对于 KEY(a,b) ：a列与b列是一种含有前缀语义的“AND”的关系，查询条件必须带上 (a=?) 或 (a=? AND b=?) 这样的前缀分区列的等值条件，才能命中分区裁剪，并被路由到特定物理分区；
对于 HASH(a,b) ：a列与b列是一种不含前缀语义的“AND”的关系，查询条件必须带上( a=? AND b=? ) 这样完全的分区列的等值条件，才能命中分区裁剪，并被路由到特定物理分区；
对于 CO_HASH(a,b) ：a列与b列是一种特殊的“OR”的关系，查询条件必须带上 (a=?) / (b=?) / (a=? OR b=?) 这样三种情况之一的分区列的等值条件，才能够命中分区裁剪，并被路由到特定物理分区。

不同点2：各个分区列支持独立使用分区函数

对于KEY/HASH：
- 若使用向量分区键，那么所有的分区列不允许使用分区函数；
- 若使用单列分区键，仅HASH策略支持对单个分区列使用分区函数，如HASH(SUBSTR(a,4))。
对于CO_HASH：
- 使用CO_HASH的分区列数目必须是 2个及以上, 最多不能超过5个；
- 各个分区列支持独立使用分区函数，例如PARTITION BY CO_HASH(RIGHT(`order_id`, 4)，RIGHT(`buyer_id`,4)) 。

CO_HASH与HASH/KEY的其它异同对比，请参见CREATE TABLE（AUTO模式）。

所有分区列都使用分区函数定义取值上的协同关系

假如业务有一张订单表orders，它的每一行录的order_id与buyer_id的后6位的数字总是相同的。那么，如果用户想对订单表orders同时按order_id与buyer_id两个列的后6位数字进行分区，并期望同一行order_id与buyer_id这两个列的等值查询条件均能路由到同一个分区的话，可以使用如下的语法定义：

CREATE TABLE t_orders(
 id bigint not null auto_increment, 
 seller_id bigint,
 order_id bigint, 
 buyer_id bigint,
 order_time datetime not null,
 primary key(id)
) 
PARTITION BY CO_HASH(
 	RIGHT(`order_id`,6) /*取c1列的后6位字符*/,
 	RIGHT(`buyer_id`,6) /*取c2列的后6位字符*/
) 
PARTITIONS 8;

部分分区列使用分区函数，部分列不使用分区函数

业务订单表orders的每一行录，它的order_id后8位数字与buyer_id（该值数字长度总是8）保持相同的，那么若想order_id与buyer_id这两个的等值查询条件都能命中分区裁剪的话，可以考虑如下的分区方案：

CREATE TABLE orders(
 id bigint not null auto_increment, 
 bid int, 
 seller_id bigint,
 buyer_id bigint,
 order_id bigint,
 order_time datetime not null,
 primary key(id)
) 
PARTITION BY CO_HASH(RIGHT(`order_id`, 8)，`buyer_id`) 
PARTITIONS 8;

创建二级分区

创建 HASH-* 二级分区

创建 HASH-HASH 二级分区

模板化用法

CREATE TABLE tb_h_h_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY HASH(bid) 
PARTITIONS 2
SUBPARTITION BY HASH(id) 
SUBPARTITIONS 4

非模板化用法

CREATE TABLE tb_h_h_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY HASH(bid) 
PARTITIONS 2
SUBPARTITION BY HASH(id) 
(
  PARTITION p1 SUBPARTITIONS 2,
  PARTITION p2 SUBPARTITIONS 4
)

创建HASH-KEY二级分区

模板化用法

CREATE TABLE tb_h_k_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY HASH(bid) 
PARTITIONS 4
SUBPARTITION BY KEY(id) 
SUBPARTITIONS 2

非模板化用法

CREATE TABLE tb_h_k_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY HASH(bid) 
PARTITIONS 2
SUBPARTITION BY KEY(id) 
(
  PARTITION p1 SUBPARTITIONS 2,
  PARTITION p2 SUBPARTITIONS 4
)

创建HASH-RANGE二级分区

模板化用法

CREATE TABLE tb_h_r_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY HASH(bid) 
PARTITIONS 2
SUBPARTITION BY RANGE(id) (
  SUBPARTITION sp1 VALUES LESS THAN(1000),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)

非模板化用法

CREATE TABLE tb_h_r_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY HASH(bid) 
PARTITIONS 2
SUBPARTITION BY RANGE(id)
(
  PARTITION p1 (
  	SUBPARTITION sp1 VALUES LESS THAN(1000),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
	),
  PARTITION p2 (
  	SUBPARTITION sp3 VALUES LESS THAN(1000),
    SUBPARTITION sp4 VALUES LESS THAN(2000),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
	)
)

创建HASH-RANGE COLUMNS二级分区

模板化用法

CREATE TABLE tb_h_rc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY HASH(bid) 
PARTITIONS 2
SUBPARTITION BY RANGE COLUMNS(name,id) (
  SUBPARTITION sp1 VALUES LESS THAN('Jack',1000),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)

非模板化用法

CREATE TABLE tb_h_rc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY HASH(bid) 
PARTITIONS 2
SUBPARTITION BY RANGE COLUMNS(name,id) 
(
  PARTITION p1 (
  	SUBPARTITION sp1 VALUES LESS THAN('Jack',1000),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
	),
  PARTITION p2 (
  	SUBPARTITION sp3 VALUES LESS THAN('Bob',1000),
    SUBPARTITION sp4 VALUES LESS THAN('Tom',2000),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE,MAXVALUE)
	)
)

创建HASH-LIST二级分区

模板化用法

CREATE TABLE tb_h_l_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY HASH(bid) 
PARTITIONS 2
SUBPARTITION BY LIST (name) (
  SUBPARTITION sp1 VALUES IN ('Jack','Bob'),
  SUBPARTITION sp2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_h_l_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY HASH(bid) 
PARTITIONS 2
SUBPARTITION BY LIST (name) 
(
  PARTITION p1 (
  	SUBPARTITION sp1 VALUES IN ('Jack','Bob'),
  	SUBPARTITION sp2 VALUES IN (DEFAULT)
	),
  PARTITION p2 (
  	SUBPARTITION sp3 VALUES IN ('Jack','Bob'),
    SUBPARTITION sp4 VALUES IN ('Tom','Lily'),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)

创建HASH-LIST COLUMNS二级分区

模板化用法

CREATE TABLE tb_h_lc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY HASH(bid) 
PARTITIONS 2
SUBPARTITION BY LIST COLUMNS (name,id) (
  SUBPARTITION sp1 VALUES IN (('Jack',1000),('Bob',2000)),
  SUBPARTITION sp2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_h_lc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY HASH(bid) 
PARTITIONS 2
SUBPARTITION BY LIST COLUMNS(name,id) 
(
  PARTITION p1 (
  	SUBPARTITION sp1 VALUES IN (('Jack',1000),('Bob', 2000)),
  	SUBPARTITION sp2 VALUES IN (DEFAULT)
	),
  PARTITION p2 (
  	SUBPARTITION sp3 VALUES IN (('Bob',1000), ('Tom',2000)),
    SUBPARTITION sp4 VALUES IN (('Lily',3000)),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)

创建KEY-*二级分区

创建KEY-HASH二级分区

模板化用法

CREATE TABLE tb_k_h_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid, name) 
PARTITIONS 2
SUBPARTITION BY HASH(id) 
SUBPARTITIONS 4

非模板化用法

CREATE TABLE tb_k_h_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid, name) 
PARTITIONS 2
SUBPARTITION BY HASH(id) 
(
  PARTITION p1 SUBPARTITIONS 2,
  PARTITION p2 SUBPARTITIONS 4
)

创建KEY-KEY二级分区

模板化用法

CREATE TABLE tb_k_k_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY KEY(id) 
SUBPARTITIONS 4

非模板化用法

CREATE TABLE tb_k_k_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY KEY(id) 
(
  PARTITION p1 SUBPARTITIONS 2,
  PARTITION p2 SUBPARTITIONS 4
)

创建KEY-RANGE二级分区

模板化用法

CREATE TABLE tb_k_r_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY RANGE(id) (
  SUBPARTITION sp1 VALUES LESS THAN(1000),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)

非模板化用法

CREATE TABLE tb_k_r_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY RANGE(id)
(
  PARTITION p1 (
  	SUBPARTITION sp1 VALUES LESS THAN(1000),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
	),
  PARTITION p2 (
  	SUBPARTITION sp3 VALUES LESS THAN(1000),
  	SUBPARTITION sp4 VALUES LESS THAN(2000),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
  )
)

创建KEY-RANGE COLUMNS二级分区

模板化用法

CREATE TABLE tb_k_rc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY RANGE COLUMNS(birthday,id) (
  SUBPARTITION sp1 VALUES LESS THAN('2000-01-01',1000),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)

非模板化用法

CREATE TABLE tb_k_rc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY RANGE COLUMNS(birthday,id)
(
  PARTITION p1 (
  	SUBPARTITION sp1 VALUES LESS THAN('2000-01-01',1000),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
	),
  PARTITION p2 (
  	SUBPARTITION sp3 VALUES LESS THAN('2000-01-01',1000),
    SUBPARTITION sp4 VALUES LESS THAN('2020-01-01',2000),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE,MAXVALUE)
	)
)

创建KEY-LIST二级分区

模板化用法

非模板化用法

CREATE TABLE tb_k_l_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY LIST(id)
(
  PARTITION p1 (
  	SUBPARTITION sp1 VALUES IN (1000,2000),
  	SUBPARTITION sp2 VALUES IN (3000,4000),
  	SUBPARTITION sp3 VALUES In (DEFAULT)
	),
  PARTITION p2 (
  	SUBPARTITION sp4 VALUES IN (5000,6000),
  	SUBPARTITION sp5 VALUES In (DEFAULT)
	)
)

创建KEY-LIST COLUMNS二级分区

模板化用法

CREATE TABLE tb_k_lc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY LIST COLUMNS(birthday,id)
(
  	SUBPARTITION sp1 VALUES IN (('2000-01-01',1000),('2000-02-01',2000)),
  	SUBPARTITION sp2 VALUES IN (('2001-01-01',3000),('2001-02-01',4000)),
  	SUBPARTITION sp3 VALUES In (DEFAULT)
)

非模板化用法

CREATE TABLE tb_k_lc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid,name) 
PARTITIONS 2
SUBPARTITION BY LIST COLUMNS(birthday,id)
(
  PARTITION p1 (
  	SUBPARTITION sp1 VALUES IN (('2000-01-01',1000),('2000-02-01',2000)),
  	SUBPARTITION sp2 VALUES IN (('2001-01-01',3000),('2001-02-01',4000)),
  	SUBPARTITION sp3 VALUES In (DEFAULT)
	),
  PARTITION p2 (
  	SUBPARTITION sp4 VALUES IN (('2002-01-10',5000),('2002-02-10',6000)),
  	SUBPARTITION sp5 VALUES In (DEFAULT)
	)
)

创建RANGE-* 二级分区

创建RANGE-HASH二级分区

模板化用法

CREATE TABLE tb_r_h_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY HASH(bid) 
SUBPARTITIONS 4
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
)

非模板化用法

CREATE TABLE tb_r_h_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY HASH(bid) 
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) SUBPARTITIONS 2,
  PARTITION p2 VALUES LESS THAN(MAXVALUE) SUBPARTITIONS 3
)

创建RANGE-KEY二级分区

模板化用法

CREATE TABLE tb_r_k_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY KEY(bid,name) 
SUBPARTITIONS 4
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
)

非模板化用法

CREATE TABLE tb_r_k_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY KEY(bid,name) 
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) SUBPARTITIONS 2,
  PARTITION p2 VALUES LESS THAN(MAXVALUE) SUBPARTITIONS 3
)

创建RANGE-RANGE二级分区

模板化用法

CREATE TABLE tb_r_r_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE(bid) 
(
  SUBPARTITION sp1 VALUES LESS THAN(100),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
)

非模板化用法

CREATE TABLE tb_r_r_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE(bid) 
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) (
  	SUBPARTITION sp1 VALUES LESS THAN(100),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
	),
  PARTITION p2 VALUES LESS THAN(MAXVALUE) (
    SUBPARTITION sp3 VALUES LESS THAN(100),
  	SUBPARTITION sp4 VALUES LESS THAN(200),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
	)
)

创建RANGE-RANGE COLUMNS二级分区

模板化用法

CREATE TABLE tb_r_rc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE COLUMNS(bid, name) 
(
  SUBPARTITION sp1 VALUES LESS THAN(100, 'Jack'),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE, MAXVALUE)
)
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
)

非模板化用法

CREATE TABLE tb_r_rc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE COLUMNS(bid, name) 
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) (
  	SUBPARTITION sp1 VALUES LESS THAN(100, 'Jack'),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE, MAXVALUE)
	),
  PARTITION p2 VALUES LESS THAN(MAXVALUE) (
  	SUBPARTITION sp3 VALUES LESS THAN(100, 'Tom'),
    SUBPARTITION sp4 VALUES LESS THAN(200, 'Bob'),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE, MAXVALUE)
	)
)

创建RANGE-LIST二级分区

模板化用法

CREATE TABLE tb_r_l_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY LIST(name) 
(
  SUBPARTITION sp1 VALUES IN ('Jack'),
  SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
)

非模板化用法

CREATE TABLE tb_r_l_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY LIST(name) 
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) (
  	SUBPARTITION sp1 VALUES IN ('Jack'),
  	SUBPARTITION sp2 VALUES IN (DEFAULT)
	),
  PARTITION p2 VALUES LESS THAN(MAXVALUE) (
  	SUBPARTITION sp3 VALUES IN ('Tom'),
    SUBPARTITION sp4 VALUES IN ('Bob'),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)

创建RANGE-LIST COLUMNS二级分区

模板化用法

CREATE TABLE tb_r_lc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY LIST COLUMNS(name,bid) 
(
  SUBPARTITION sp1 VALUES IN (('Jack', 100), ('Bob',200)),
  SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
)

非模板化用法

CREATE TABLE tb_r_lc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE(TO_DAYS(birthday)) 
SUBPARTITION BY LIST COLUMNS(name,bid) 
(
  PARTITION p1 VALUES LESS THAN(TO_DAYS('2000-01-01')) (
  	SUBPARTITION sp1 VALUES IN (('Jack', 100), ('Bob',200)),
  	SUBPARTITION sp2 VALUES IN (DEFAULT)
	),
  PARTITION p2 VALUES LESS THAN(MAXVALUE) (
  	SUBPARTITION sp3 VALUES IN (('Jack', 100), ('Bob',200)),
    SUBPARTITION sp4 VALUES IN (('Lily', 200), ('Marry',400)),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)

创建RANGE COLUMNS-*二级分区

创建RANGE COLUMNS-HASH二级分区

模板化用法

CREATE TABLE tb_rc_h_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE COLUMNS(birthday,name) 
SUBPARTITION BY HASH(bid) 
SUBPARTITIONS 4
(
  PARTITION p1 VALUES LESS THAN('2000-01-01','Jack'),
  PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)

非模板化用法

CREATE TABLE tb_rc_h_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE COLUMNS(birthday,name) 
SUBPARTITION BY HASH(bid) 
(
  PARTITION p1 VALUES LESS THAN('2000-01-01','Jack') SUBPARTITIONS 2,
  PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE) SUBPARTITIONS 3
)

创建RANGE COLUMNS-KEY二级分区

模板化用法

CREATE TABLE tb_rc_k_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE COLUMNS(birthday,name) 
SUBPARTITION BY KEY(bid,id) 
SUBPARTITIONS 4
(
  PARTITION p1 VALUES LESS THAN('2000-01-01','Jack'),
  PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)

非模板化用法

CREATE TABLE tb_rc_k_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE COLUMNS(birthday,name) 
SUBPARTITION BY KEY(bid,id) 
(
  PARTITION p1 VALUES LESS THAN('2000-01-01','Jack') SUBPARTITIONS 2,
  PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE) SUBPARTITIONS 3
)

创建RANGE COLUMNS-RANGE二级分区

模板化用法

CREATE TABLE tb_rc_r_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY RANGE(bid) 
(
  SUBPARTITION sp1 VALUES LESS THAN(100),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)
(
  PARTITION p1 VALUES LESS THAN('2000-01-01','Jack'),
  PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)

非模板化用法

CREATE TABLE tb_rc_r_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY RANGE(bid) 
(
  PARTITION p1 VALUES LESS THAN('2000-01-01','Jack') (
  	SUBPARTITION sp1 VALUES LESS THAN(100),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
	),
  PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE) (
  	SUBPARTITION sp3 VALUES LESS THAN(100),
    SUBPARTITION sp4 VALUES LESS THAN(200),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
	)
)

创建RANGE COLUMNS-RANGE COLUMNS二级分区

模板化用法

CREATE TABLE tb_rc_rc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY RANGE COLUMNS(bid,id) 
(
  SUBPARTITION sp1 VALUES LESS THAN(100,1000),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)
(
  PARTITION p1 VALUES LESS THAN('2000-01-01','Jack'),
  PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)

非模板化用法

CREATE TABLE tb_rc_rc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY RANGE(bid,id) 
(
  PARTITION p1 VALUES LESS THAN('2000-01-01','Jack') (
  	SUBPARTITION sp1 VALUES LESS THAN(100,1000),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
	),
  PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE) (
  	SUBPARTITION sp3 VALUES LESS THAN(200,1000),
    SUBPARTITION sp4 VALUES LESS THAN(200,2000),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE,MAXVALUE)
	)
)

创建RANGE COLUMNS-LIST二级分区

模板化用法

CREATE TABLE tb_rc_l_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY LIST(bid) 
(
  SUBPARTITION sp1 VALUES IN (100,200),
  SUBPARTITION sp2 VALUES IN (300,400),
  SUBPARTITION sp3 VALUES IN (DEFAULT)
)
(
  PARTITION p1 VALUES LESS THAN('2000-01-01','Jack'),
  PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)

非模板化用法

CREATE TABLE tb_rc_l_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY LIST(bid) 
(
  PARTITION p1 VALUES LESS THAN('2000-01-01','Jack') (
  	SUBPARTITION sp1 VALUES IN (100,200),
  	SUBPARTITION sp2 VALUES IN (DEFAULT)
	),
  PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE) (
  	SUBPARTITION sp3 VALUES IN (100,200),
  	SUBPARTITION sp4 VALUES IN (300,400),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)

创建RANGE COLUMNS-LIST COLUMNS二级分区

模板化用法

CREATE TABLE tb_rc_lc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY LIST COLUMNS(bid,id) 
(
  SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000)),
  SUBPARTITION sp2 VALUES IN ((300,300),(400,2000)),
  SUBPARTITION sp3 VALUES IN (DEFAULT)
)
(
  PARTITION p1 VALUES LESS THAN('2000-01-01','Jack'),
  PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)

非模板化用法

CREATE TABLE tb_rc_lc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY RANGE COLUMNS(birthday,name)
SUBPARTITION BY LIST COLUMNS(bid,id) 
(
  PARTITION p1 VALUES LESS THAN('2000-01-01','Jack') (
  	SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000)),
  	SUBPARTITION sp2 VALUES IN ((300,3000),(400,2000)),
  	SUBPARTITION sp3 VALUES IN (DEFAULT)
	),
  PARTITION p2 VALUES LESS THAN(MAXVALUE,MAXVALUE) (
  	SUBPARTITION sp4 VALUES IN ((400,4000),(500,5000)),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)

创建LIST-*二级分区

创建LIST-HASH二级分区

模板化用法

CREATE TABLE tb_l_h_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY HASH(bid) 
SUBPARTITIONS 4
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_l_h_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY HASH(bid) 
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')) SUBPARTITIONS 3,
  PARTITION p2 VALUES IN (DEFAULT) SUBPARTITIONS 2
)

创建LIST-KEY二级分区

模板化用法

CREATE TABLE tb_l_k_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY KEY(bid, name) 
SUBPARTITIONS 4
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_l_k_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY HASH(bid,name) 
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01')) SUBPARTITIONS 3,
  PARTITION p2 VALUES IN (DEFAULT) SUBPARTITIONS 2
)

创建LIST-RANGE二级分区

模板化用法

CREATE TABLE tb_l_r_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE(bid) 
(
  SUBPARTITION sp1 VALUES LESS THAN(100),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_l_r_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE(bid) 

(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')) (
  	SUBPARTITION sp1 VALUES LESS THAN(100),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
	),
  PARTITION p2 VALUES IN (DEFAULT) (
  	SUBPARTITION sp3 VALUES LESS THAN(100),
    SUBPARTITION sp4 VALUES LESS THAN(200),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
	)
)

创建LIST-RANGE COLUMNS二级分区

模板化用法

CREATE TABLE tb_l_rc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE COLUMNS(bid, id) 
(
  SUBPARTITION sp1 VALUES LESS THAN(100,1000),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_l_rc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY RANGE COLUMNS(bid, id) 
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')) (
  	SUBPARTITION sp1 VALUES LESS THAN(100,1000),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
	),
  PARTITION p2 VALUES IN (DEFAULT) (
  	SUBPARTITION sp3 VALUES LESS THAN(100,1000),
    SUBPARTITION sp4 VALUES LESS THAN(200,2000),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE,MAXVALUE)
	)
)

创建LIST-LIST二级分区

模板化用法

CREATE TABLE tb_l_l_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY LIST(id) 
(
  SUBPARTITION sp1 VALUES IN (1000,2000,3000),
  SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_l_l_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY LIST(id) 
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')) (
  	SUBPARTITION sp1 VALUES IN (1000,2000,3000),
  	SUBPARTITION sp2 VALUES IN (DEFAULT)
	),
  PARTITION p2 VALUES IN (DEFAULT) (
  	SUBPARTITION sp3 VALUES IN (1000,2000,3000),
    SUBPARTITION sp4 VALUES IN (4000,5000),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)

创建LIST-LIST COLUMNS二级分区

模板化用法

CREATE TABLE tb_l_lc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY LIST COLUMNS(bid,id) 
(
  SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000),(300,3000)),
  SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_l_lc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST(TO_DAYS(birthday)) 
SUBPARTITION BY LIST COLUMNS(bid,id) 
(
  PARTITION p1 VALUES IN (TO_DAYS('2000-01-01'), TO_DAYS('2001-01-01'), TO_DAYS('2002-01-01')) (
  	SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000),(300,3000)),
  	SUBPARTITION sp2 VALUES IN (DEFAULT)
	),
  PARTITION p2 VALUES IN (DEFAULT) (
  	SUBPARTITION sp3 VALUES IN ((100,1000),(200,2000),(300,3000)),
    SUBPARTITION sp4 VALUES IN ((400,4000),(500,5000)),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)

创建LIST COLUMNS-*二级分区

创建LIST COLUMNS-HASH二级分区

模板化用法

CREATE TABLE tb_lc_h_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY HASH(bid) 
SUBPARTITIONS 4
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_lc_h_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY HASH(bid) 
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')) SUBPARTITIONS 3,
  PARTITION p2 VALUES IN (DEFAULT) SUBPARTITIONS 2
)

创建LIST COLUMNS-KEY二级分区

模板化用法

CREATE TABLE tb_lc_k_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY KEY(bid,id) 
SUBPARTITIONS 4
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_lc_k_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY KEY(bid,id) 
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')) SUBPARTITIONS 3,
  PARTITION p2 VALUES IN (DEFAULT) SUBPARTITIONS 2
)

创建LIST COLUMNS-RANGE二级分区

模板化用法

CREATE TABLE tb_lc_r_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY RANGE(bid) 
(
  SUBPARTITION sp1 VALUES LESS THAN(100),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
)
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_lc_r_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY RANGE(bid) 
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')) (
  	SUBPARTITION sp1 VALUES LESS THAN(100),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE)
	),
  PARTITION p2 VALUES IN (DEFAULT) (
  	SUBPARTITION sp3 VALUES LESS THAN(100),
    SUBPARTITION sp4 VALUES LESS THAN(200),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE)
	)
)

创建LIST COLUMNS-RANGE COLUMNS二级分区

模板化用法

CREATE TABLE tb_lc_rc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY RANGE COLUMNS(bid,id) 
(
  SUBPARTITION sp1 VALUES LESS THAN(100,1000),
  SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
)
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_lc_rc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY RANGE COLUMNS(bid,id) 
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')) (
  	SUBPARTITION sp1 VALUES LESS THAN(100,1000),
  	SUBPARTITION sp2 VALUES LESS THAN(MAXVALUE,MAXVALUE)
	),
  PARTITION p2 VALUES IN (DEFAULT) (
  	SUBPARTITION sp3 VALUES LESS THAN(100,1000),
    SUBPARTITION sp4 VALUES LESS THAN(200,2000),
  	SUBPARTITION sp5 VALUES LESS THAN(MAXVALUE,MAXVALUE)
	)
)

创建LIST COLUMNS-LIST二级分区

模板化用法

CREATE TABLE tb_lc_l_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY LIST(id) 
(
  SUBPARTITION sp1 VALUES IN (1000,2000,3000),
  SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_lc_l_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY LIST(id) 
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')) (
  	SUBPARTITION sp1 VALUES IN (1000,2000,3000),
  	SUBPARTITION sp2 VALUES IN (DEFAULT)
	),
  PARTITION p2 VALUES IN (DEFAULT) (
  	SUBPARTITION sp3 VALUES IN (1000,2000),
    SUBPARTITION sp4 VALUES IN (3000,4000,5000),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)

创建LIST COLUMNS-LIST COLUMNS二级分区

模板化用法

CREATE TABLE tb_lc_lc_tp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY LIST COLUMNS(bid,id) 
(
  SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000),(300,3000)),
  SUBPARTITION sp2 VALUES IN (DEFAULT)
)
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')),
  PARTITION p2 VALUES IN (DEFAULT)
)

非模板化用法

CREATE TABLE tb_lc_lc_ntp(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY LIST COLUMNS(birthday,name) 
SUBPARTITION BY LIST COLUMNS(bid,id) 
(
  PARTITION p1 VALUES IN (('2000-01-01','Jack'),('2001-01-01','Bob')) (
  	SUBPARTITION sp1 VALUES IN ((100,1000),(200,2000),(300,3000)),
  	SUBPARTITION sp2 VALUES IN (DEFAULT)
	),
  PARTITION p2 VALUES IN (DEFAULT) (
  	SUBPARTITION sp3 VALUES IN ((100,1000),(200,2000),(300,3000)),
    SUBPARTITION sp4 VALUES IN ((400,4000),(500,5000)),
  	SUBPARTITION sp5 VALUES IN (DEFAULT)
	)
)

分区选择语法

AUTO模式数据库的分区表中允许通过MySQL分区选择语法直接读写某个分区的数据。

语法

SELECT ... FROM tbl_name [PARTITION ( part_name[, part_name, ...] )]

选择一级分区

示例1：查询指定分区表的一个或多个一级分区数据

CREATE TABLE tb_k(
    ->  id bigint not null auto_increment, 
    ->  bid int, 
    ->  name varchar(30),
    ->  birthday datetime not null,
    ->  primary key(id)
    -> ) 
    -> PARTITION BY KEY(id, bid) 
    -> PARTITIONS 8;
Query OK, 0 rows affected (2.06 sec)

explain SELECT * FROM tb_k PARTITION( p1,p2 );
+-----------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                 |
+-----------------------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true)                                                                                               |
|   LogicalView(tables="tb_k[p1,p2]", shardCount=2, sql="SELECT `id`, `bid`, `name`, `birthday` FROM `tb_k` AS `tb_k`") |
| HitCache:false                                                                                                        |
| Source:PLAN_CACHE                                                                                                     |
| TemplateId: e210fe50                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.05 sec)

示例2：删除指定分区表的一个或多个一级分区数据

CREATE TABLE tb_k(
    ->  id bigint not null auto_increment, 
    ->  bid int, 
    ->  name varchar(30),
    ->  birthday datetime not null,
    ->  primary key(id)
    -> ) 
    -> PARTITION BY KEY(id, bid) 
    -> PARTITIONS 8;
Query OK, 0 rows affected (3.59 sec)

explain DELETE FROM tb_k PARTITION( p1,p2 );
+---------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                           |
+---------------------------------------------------------------------------------+
| LogicalModifyView(tables="tb_k[p1,p2]", shardCount=2, sql="DELETE FROM `tb_k`") |
| HitCache:false                                                                  |
| Source:PLAN_CACHE                                                               |
| TemplateId: 19bd2adf                                                            |
+---------------------------------------------------------------------------------+

选择二级分区

示例3：查询指定分区表的一个或多个一级分区或二级分区数据

CREATE TABLE tb_k_k_tp(
    ->  id bigint not null auto_increment, 
    ->  bid int, 
    ->  name varchar(30),
    ->  birthday datetime not null,
    ->  primary key(id)
    -> ) 
    -> PARTITION BY KEY(bid,name) 
    -> PARTITIONS 2
    -> SUBPARTITION BY KEY(id) 
    -> SUBPARTITIONS 4;
Query OK, 0 rows affected (1.94 sec)

explain SELECT * FROM tb_k_k_tp PARTITION( p1sp1,p1sp2 )/*指定两个二级分区*/;
+--------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true)                                                                                                                    |
|   LogicalView(tables="tb_k_k_tp[p1sp1,p1sp2]", shardCount=2, sql="SELECT `id`, `bid`, `name`, `birthday` FROM `tb_k_k_tp` AS `tb_k_k_tp`") |
| HitCache:false                                                                                                                             |
| Source:PLAN_CACHE                                                                                                                          |
| TemplateId: 38bba74d                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.03 sec)

explain SELECT * FROM tb_k_k_tp PARTITION( p1,p2sp2 )/*指定一个一级分区，一个二级分区*/;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true)                                                                                                                                      |
|   LogicalView(tables="tb_k_k_tp[p1sp1,p1sp2,p1sp3,p1sp4,p2sp2]", shardCount=5, sql="SELECT `id`, `bid`, `name`, `birthday` FROM `tb_k_k_tp` AS `tb_k_k_tp`") |
| HitCache:false                                                                                                                                               |
| Source:PLAN_CACHE                                                                                                                                            |
| TemplateId: dbc4cb56                                                                                                                                         |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

分区名命名规则

当您需要访问或修改指定的一级分区或二级分区时，通常语法里需要指定具体的一级分区或二级分区的名称。因此，这里对分区表中一些会自动生成分区名称的规则进行说明。

分区表中涉及到自动生成分区名称的有以下几种情况：

分区表或全局索引表使用HASH或KEY进行分区（包括自动分区表）；
分区表使用模板化二级分区进行分区；
哈希分区表被手动执行分区分裂变更。

一级分区命名规则

一级分区会自动生成分区名称的场景主要是使用哈希分区策略（Hash或Key）并仅指定哈希分区数目（包括手动分区表与自动分区表）。

使用哈希策略进行分区，其分区名字的命名规则为：p+分区下标（从1开始计数）。

CREATE TABLE test_tbl_part_name(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(id) 
PARTITIONS 4

例如，若使用上述语句创建分区表，生成的分区名称为：第1个哈希分区的名称是p1，第2个哈希分区的名称是p2，如此类推。以下是实际验证结果：

CREATE TABLE test_tbl_part_name (
    ->  id bigint not null auto_increment, 
    ->  primary key(id)
    -> ) 
    -> PARTITION BY KEY(id) 
    -> PARTITIONS 4;
Query OK, 0 rows affected (1.13 sec)

select table_name,partition_name from information_schema.partitions where table_name='test_tbl_part_name';
+--------------------+----------------+
| table_name         | partition_name |
+--------------------+----------------+
| test_tbl_part_name | p1             |
| test_tbl_part_name | p2             |
| test_tbl_part_name | p3             |
| test_tbl_part_name | p4             |
+--------------------+----------------+
4 rows in set (0.59 sec)

如果用户通过ALTER语句对分区表的特定分区进行一些分裂变更，那么分裂生成的新分区名字的命名规则为：p+当前最大分区下标+1 。示例如下：

alter table test_tbl_part_name split partition p1;
Query OK, 0 rows affected (5.83 sec)

select table_name,partition_name from information_schema.partitions where table_name='test_tbl_part_name';
+--------------------+----------------+
| table_name         | partition_name |
+--------------------+----------------+
| test_tbl_part_name | p5             |
| test_tbl_part_name | p6             |
| test_tbl_part_name | p2             |
| test_tbl_part_name | p3             |
| test_tbl_part_name | p4             |
+--------------------+----------------+
5 rows in set (0.18 sec)

二级分区命名规则

二级分区有模板化与非模板化两种用法，因此二级分区名称的生成规则比一级分区的稍微复杂一些。

对于模板化二级分区，哈希分区模板的命名规则是：sp+二级分区下标（从1开始计数）。

对于非模板化二级分区，各个一级分区下的哈希分区名字是独立生成的，其命名规则是：sp+二级分区下标（从1开始计数，各个一级分区独立计数）。

对于所有二级分区，其分区实际名字的生成规则是：一级分区名字+二级分区（模板）名字。

例如，使用以下分区定义创建模板化二级分区表：

CREATE TABLE test_tbl_part_name2(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(bid) 
PARTITIONS 2
SUBPARTITION BY KEY(id) 
SUBPARTITIONS 4

这个表的所有二级分区的名称生成过程如下：

自动生成一级分区的哈希分区：p1，p2；
自动生成二级分区模板的哈希分区名，sp1，sp2，sp3，sp4；
最后生成二级分区的实际名字（物理分区名字，一级分区名+二级分区名）：
- p1sp1，p1sp2，p1sp3，p1sp4
- p2sp1，p2sp2，p2sp3，p2sp4

以下是分区信息的查询结果：

CREATE TABLE test_tbl_part_name2(
    ->  id bigint not null auto_increment, 
    ->  bid int, 
    ->  name varchar(30),
    ->  birthday datetime not null,
    ->  primary key(id)
    -> ) 
    -> PARTITION BY KEY(bid) 
    -> PARTITIONS 2
    -> SUBPARTITION BY KEY(id) 
    -> SUBPARTITIONS 4;
Query OK, 0 rows affected (1.40 sec)

select table_name,partition_name,subpartition_name from information_schema.partitions where table_schema='autodb2' and table_name='test_tbl_part_name2';
+---------------------+----------------+-------------------+
| table_name          | partition_name | subpartition_name |
+---------------------+----------------+-------------------+
| test_tbl_part_name2 | p1             | p1sp1             |
| test_tbl_part_name2 | p1             | p1sp2             |
| test_tbl_part_name2 | p1             | p1sp3             |
| test_tbl_part_name2 | p1             | p1sp4             |
| test_tbl_part_name2 | p2             | p2sp1             |
| test_tbl_part_name2 | p2             | p2sp2             |
| test_tbl_part_name2 | p2             | p2sp3             |
| test_tbl_part_name2 | p2             | p2sp4             |
+---------------------+----------------+-------------------+
8 rows in set (0.12 sec)

分区信息查看

PolarDB-X支持通过多种方式查询分区表的元数据字典（例如分区拓扑等）。

使用PARTITIONS视图查询

PolarDB-X兼容MySQL的INFORMATION_SCHEMA.PARTITIONS的视图查询，支持通过PARTITIONS视图查询各个一级分区及其二级分区的相关元信息，例如：

select *
    -> from information_schema.partitions 
    -> where table_schema='autodb2' and table_name='test_tbl_part_name2'
    -> order by partition_name, subpartition_name;
+---------------+--------------+---------------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+--------------------------+------------+----------------+-------------+-----------------+--------------+-----------+-------------+-------------+------------+----------+-------------------+-----------+-----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME          | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | SUBPARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME |
+---------------+--------------+---------------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+--------------------------+------------+----------------+-------------+-----------------+--------------+-----------+-------------+-------------+------------+----------+-------------------+-----------+-----------------+
| def           | autodb2      | test_tbl_part_name2 | p1             | p1sp1             |                          1 |                             1 | KEY              | KEY                 | bid                  | id                      | 1                     | -4611686018427387899     |          0 |              0 |       16384 |            NULL |        16384 |      NULL | NULL        | NULL        | NULL       |     NULL |                   | default   | NULL            |
| def           | autodb2      | test_tbl_part_name2 | p1             | p1sp2             |                          1 |                             2 | KEY              | KEY                 | bid                  | id                      | 1                     | 3                        |          0 |              0 |       16384 |            NULL |        16384 |      NULL | NULL        | NULL        | NULL       |     NULL |                   | default   | NULL            |
| def           | autodb2      | test_tbl_part_name2 | p1             | p1sp3             |                          1 |                             3 | KEY              | KEY                 | bid                  | id                      | 1                     | 4611686018427387905      |          0 |              0 |       16384 |            NULL |        16384 |      NULL | NULL        | NULL        | NULL       |     NULL |                   | default   | NULL            |
| def           | autodb2      | test_tbl_part_name2 | p1             | p1sp4             |                          1 |                             4 | KEY              | KEY                 | bid                  | id                      | 1                     | 9223372036854775807      |          0 |              0 |       16384 |            NULL |        16384 |      NULL | NULL        | NULL        | NULL       |     NULL |                   | default   | NULL            |
| def           | autodb2      | test_tbl_part_name2 | p2             | p2sp1             |                          2 |                             1 | KEY              | KEY                 | bid                  | id                      | 9223372036854775807   | -4611686018427387899     |          0 |              0 |       16384 |            NULL |        16384 |      NULL | NULL        | NULL        | NULL       |     NULL |                   | default   | NULL            |
| def           | autodb2      | test_tbl_part_name2 | p2             | p2sp2             |                          2 |                             2 | KEY              | KEY                 | bid                  | id                      | 9223372036854775807   | 3                        |          0 |              0 |       16384 |            NULL |        16384 |      NULL | NULL        | NULL        | NULL       |     NULL |                   | default   | NULL            |
| def           | autodb2      | test_tbl_part_name2 | p2             | p2sp3             |                          2 |                             3 | KEY              | KEY                 | bid                  | id                      | 9223372036854775807   | 4611686018427387905      |          0 |              0 |       16384 |            NULL |        16384 |      NULL | NULL        | NULL        | NULL       |     NULL |                   | default   | NULL            |
| def           | autodb2      | test_tbl_part_name2 | p2             | p2sp4             |                          2 |                             4 | KEY              | KEY                 | bid                  | id                      | 9223372036854775807   | 9223372036854775807      |          0 |              0 |       16384 |            NULL |        16384 |      NULL | NULL        | NULL        | NULL       |     NULL |                   | default   | NULL            |
+---------------+--------------+---------------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+--------------------------+------------+----------------+-------------+-----------------+--------------+-----------+-------------+-------------+------------+----------+-------------------+-----------+-----------------+
8 rows in set (0.14 sec)

使用SHOW TOPOLOGY查询

若需要简单查询分区表的整体拓扑以及各个分区的物理位置（物理库表的所在DN），也可以采用SHOW TOPOLOGY FROM #table_name命令快速查看（如下所示）：

SHOW TOPOLOGY FROM test_tbl_part_name2 ORDER BY PARTITION_NAME, SUBPARTITION_NAME;
+------+----------------------+--------------------------------+----------------+-------------------+----------------+---------------------------+
| ID   | GROUP_NAME           | TABLE_NAME                     | PARTITION_NAME | SUBPARTITION_NAME | PHY_DB_NAME    | DN_ID                     |
+------+----------------------+--------------------------------+----------------+-------------------+----------------+---------------------------+
|    4 | AUTODB2_P00000_GROUP | test_tbl_part_name2_xds7_00000 | p1             | p1sp1             | autodb2_p00000 | polardbx-storage-0-master |
|    0 | AUTODB2_P00001_GROUP | test_tbl_part_name2_xds7_00001 | p1             | p1sp2             | autodb2_p00001 | polardbx-storage-1-master |
|    5 | AUTODB2_P00000_GROUP | test_tbl_part_name2_xds7_00002 | p1             | p1sp3             | autodb2_p00000 | polardbx-storage-0-master |
|    1 | AUTODB2_P00001_GROUP | test_tbl_part_name2_xds7_00003 | p1             | p1sp4             | autodb2_p00001 | polardbx-storage-1-master |
|    6 | AUTODB2_P00000_GROUP | test_tbl_part_name2_xds7_00004 | p2             | p2sp1             | autodb2_p00000 | polardbx-storage-0-master |
|    2 | AUTODB2_P00001_GROUP | test_tbl_part_name2_xds7_00005 | p2             | p2sp2             | autodb2_p00001 | polardbx-storage-1-master |
|    7 | AUTODB2_P00000_GROUP | test_tbl_part_name2_xds7_00006 | p2             | p2sp3             | autodb2_p00000 | polardbx-storage-0-master |
|    3 | AUTODB2_P00001_GROUP | test_tbl_part_name2_xds7_00007 | p2             | p2sp4             | autodb2_p00001 | polardbx-storage-1-master |
+------+----------------------+--------------------------------+----------------+-------------------+----------------+---------------------------+
8 rows in set (0.07 sec)

使用TABLE_DETAILS视图查询

若需要查询分区表各分区的数据占比及其读写情况，可以通过INFORMATION_SCHEMA.TABLE_DETAIL视图进行，例如：

select table_name, partition_name, subpartition_name, percent, 
    -> rows_read, rows_inserted, rows_updated, rows_deleted from information_schema.table_detail  
    -> where table_schema='autodb2' and table_name='test_tbl_part_name2' 
    -> order by partition_name, subpartition_name;
+---------------------+----------------+-------------------+--------------------------------------+-----------+---------------+--------------+--------------+
| table_name          | partition_name | subpartition_name | percent                              | rows_read | rows_inserted | rows_updated | rows_deleted |
+---------------------+----------------+-------------------+--------------------------------------+-----------+---------------+--------------+--------------+
| test_tbl_part_name2 | p1             | p1sp1             | 0.00%├-------------------------┤     |         0 |             0 |            0 |            0 |
| test_tbl_part_name2 | p1             | p1sp2             | 0.00%├-------------------------┤     |         0 |             0 |            0 |            0 |
| test_tbl_part_name2 | p1             | p1sp3             | 0.00%├-------------------------┤     |         0 |             0 |            0 |            0 |
| test_tbl_part_name2 | p1             | p1sp4             | 0.00%├-------------------------┤     |         0 |             0 |            0 |            0 |
| test_tbl_part_name2 | p2             | p2sp1             | 0.00%├-------------------------┤     |         0 |             0 |            0 |            0 |
| test_tbl_part_name2 | p2             | p2sp2             | 0.00%├-------------------------┤     |         0 |             0 |            0 |            0 |
| test_tbl_part_name2 | p2             | p2sp3             | 0.00%├-------------------------┤     |         0 |             0 |            0 |            0 |
| test_tbl_part_name2 | p2             | p2sp4             | 0.00%├-------------------------┤     |         0 |             0 |            0 |            0 |
+---------------------+----------------+-------------------+--------------------------------------+-----------+---------------+--------------+--------------+
8 rows in set (0.10 sec)

通过INFORMATION_SCHEMA.TABLE_DETAIL视图还能够查询PolarDB-X所特有的表的元数据信息。例如分区表所属的表组、分区表所属于的全局索引表等信息。