文档反馈官招募中，报名立赚积分兑换代金券！> HOT
文档中心>分布式数据库 TDSQL>操作指南>系统表和系统视图>INFORMATION_SCHEMA>PARTITIONS_VERBOSE

PARTITIONS_VERBOSE

最近更新时间：2025-11-18 10:10:23

我的收藏

本页目录：

功能

INFORMATION_SCHEMA.PARTITIONS_VERBOSE用于展示比标准 PARTITIONS表更详细的分区表信息。主要区别在于：
PARTITIONS视图：仅显示实际的分区/子分区信息，对于有子分区的表，不会显示一级分区的汇总信息。
PARTITIONS_VERBOSE视图：同时显示一级分区和二级分区的完整信息，提供更全面的分区结构视图。

字段说明

字段名
类型
描述
TABLE_CATALOG
varchar(64)
表所属的目录名称，通常为 'def'
TABLE_SCHEMA
varchar(64)
表所属的数据库名称
TABLE_NAME
varchar(64)
表名称
PARTITION_NAME
varchar(64)
分区名称
SUBPARTITION_NAME
varchar(64)
如果该行表示一个子分区（subpartition），则为子分区的名称；否则为 NULL。
PARTITION_ORDINAL_POSITION
int unsigned
分区在表中的位置序号
SUBPARTITION_ORDINAL_POSITION
int unsigned
子分区在分区中的位置序号
PARTITION_METHOD
varchar(13)
分区方法（RANGE、LIST、HASH、KEY 等）
SUBPARTITION_METHOD
varchar(13)
子分区方法
PARTITION_EXPRESSION
varchar(2048)
分区表达式
SUBPARTITION_EXPRESSION
text
子分区表达式
PARTITION_DESCRIPTION
text
分区描述（如 RANGE 分区的边界值）
TABLE_ROWS
bigint unsigned
分区中的估计行数
AVG_ROW_LENGTH
bigint unsigned
分区的平均行长度
DATA_LENGTH
bigint unsigned
分区数据长度（字节）
MAX_DATA_LENGTH
bigint unsigned
分区最大数据长度
INDEX_LENGTH
bigint unsigned
分区索引长度
DATA_FREE
bigint unsigned
分区中未使用的空间
CREATE_TIME
timestamp
分区创建时间
UPDATE_TIME
datetime
分区最后更新时间
CHECK_TIME
datetime
分区最后检查时间
CHECKSUM
bigint
分区校验和值
PARTITION_COMMENT
text
分区注释信息
NODEGROUP
varchar(256)
节点组信息
TABLESPACE_NAME
varchar(268)
表空间名称
TINDEX_ID
int unsigned
表或者分区的 TINDEX_ID
TINDEX_ID_STORAGE_FORMAT
varchar(8)
表索引 ID 存储格式
DATA_SPACE_TYPE
varchar(128)
数据空间类型
SE_PRIVATE_DATA
mediumtext
存储引擎私有数据
TABLE_SCHEMA_VERSION
int unsigned
表结构版本号
TABLE_SCHEMA_STATUS
smallint unsigned
表结构状态
TABLE_EXTRA_INFO
mediumtext
表额外信息（JSON 格式）
TABLE_SE_PRIVATE_DATA
mediumtext
表存储引擎私有数据

示例

1. 创建了一个具有两级分区的 sales 表：
一级分区：按年份范围分区（RANGE），共2个分区（p0, p1）。
二级分区：按月哈希分区（HASH），每个一级分区下各有2个子分区。
2. 查询PARTITIONS_VERBOSE视图：返回6行数据，包含：
2行一级分区信息（SUBPARTITION_NAME为NULL）。
4行二级分区信息。
#创建分区表示例
tdsql > CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date))
SUBPARTITION BY HASH(MONTH(sale_date))
SUBPARTITIONS 2 (
    PARTITION p0 VALUES LESS THAN (2023),
    PARTITION p1 VALUES LESS THAN (2025)
);

#查询分区信息
tdsql > select * from INFORMATION_SCHEMA.partitions_verbose where TABLE_SCHEMA="test" and TABLE_NAME="sales"\\G;
*************************** 1. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: sales
               PARTITION_NAME: p0
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: HASH
         PARTITION_EXPRESSION: year(`sale_date`)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 2023
                   TABLE_ROWS: NULL
               AVG_ROW_LENGTH: NULL
                  DATA_LENGTH: NULL
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: NULL
                    DATA_FREE: NULL
                  CREATE_TIME: 2025-11-09 16:34:32
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
                    TINDEX_ID: 10031
     TINDEX_ID_STORAGE_FORMAT: 0000272F
              DATA_SPACE_TYPE: DATA_SPACE_TYPE_USER
              SE_PRIVATE_DATA: create_data_obj_task_id=29572817330634866;distribution_policy_id=1;hidden_pk_autoinc_tindex_id=0;schema_status=0;
         TABLE_SCHEMA_VERSION: 1
          TABLE_SCHEMA_STATUS: 0
             TABLE_EXTRA_INFO: {"version":3,"create_ts":0}
        TABLE_SE_PRIVATE_DATA: autoinc_version=1;create_data_obj_task_id=29572817330634866;distribution_policy_id=1;origin_db=test;origin_table=sales;partition_policy_id=0;sync_table=0;
*************************** 2. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: sales
               PARTITION_NAME: p0
            SUBPARTITION_NAME: p0sp0
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: 1
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: HASH
         PARTITION_EXPRESSION: year(`sale_date`)
      SUBPARTITION_EXPRESSION: month(`sale_date`)
        PARTITION_DESCRIPTION: 2023
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 0
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2025-11-09 16:34:32
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
                    TINDEX_ID: 10032
     TINDEX_ID_STORAGE_FORMAT: 00002730
              DATA_SPACE_TYPE: DATA_SPACE_TYPE_USER
              SE_PRIVATE_DATA: create_data_obj_task_id=29572817330634866;distribution_policy_id=1;hidden_pk_autoinc_tindex_id=10033;schema_status=0;
         TABLE_SCHEMA_VERSION: 1
          TABLE_SCHEMA_STATUS: 0
             TABLE_EXTRA_INFO: {"version":3,"create_ts":0}
        TABLE_SE_PRIVATE_DATA: autoinc_version=1;create_data_obj_task_id=29572817330634866;distribution_policy_id=1;origin_db=test;origin_table=sales;partition_policy_id=0;sync_table=0;
*************************** 3. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: sales
               PARTITION_NAME: p0
            SUBPARTITION_NAME: p0sp1
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: 2
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: HASH
         PARTITION_EXPRESSION: year(`sale_date`)
      SUBPARTITION_EXPRESSION: month(`sale_date`)
        PARTITION_DESCRIPTION: 2023
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 0
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2025-11-09 16:34:32
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
                    TINDEX_ID: 10034
     TINDEX_ID_STORAGE_FORMAT: 00002732
              DATA_SPACE_TYPE: DATA_SPACE_TYPE_USER
              SE_PRIVATE_DATA: create_data_obj_task_id=29572817330634866;distribution_policy_id=1;hidden_pk_autoinc_tindex_id=10035;schema_status=0;
         TABLE_SCHEMA_VERSION: 1
          TABLE_SCHEMA_STATUS: 0
             TABLE_EXTRA_INFO: {"version":3,"create_ts":0}
        TABLE_SE_PRIVATE_DATA: autoinc_version=1;create_data_obj_task_id=29572817330634866;distribution_policy_id=1;origin_db=test;origin_table=sales;partition_policy_id=0;sync_table=0;
*************************** 4. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: sales
               PARTITION_NAME: p1
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: HASH
         PARTITION_EXPRESSION: year(`sale_date`)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 2025
                   TABLE_ROWS: NULL
               AVG_ROW_LENGTH: NULL
                  DATA_LENGTH: NULL
              MAX_DATA_LENGTH: NULL
                 INDEX_LENGTH: NULL
                    DATA_FREE: NULL
                  CREATE_TIME: 2025-11-09 16:34:32
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
                    TINDEX_ID: 10036
     TINDEX_ID_STORAGE_FORMAT: 00002734
              DATA_SPACE_TYPE: DATA_SPACE_TYPE_USER
              SE_PRIVATE_DATA: create_data_obj_task_id=29572817330634866;distribution_policy_id=1;hidden_pk_autoinc_tindex_id=0;schema_status=0;
         TABLE_SCHEMA_VERSION: 1
          TABLE_SCHEMA_STATUS: 0
             TABLE_EXTRA_INFO: {"version":3,"create_ts":0}
        TABLE_SE_PRIVATE_DATA: autoinc_version=1;create_data_obj_task_id=29572817330634866;distribution_policy_id=1;origin_db=test;origin_table=sales;partition_policy_id=0;sync_table=0;
*************************** 5. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: sales
               PARTITION_NAME: p1
            SUBPARTITION_NAME: p1sp0
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: 1
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: HASH
         PARTITION_EXPRESSION: year(`sale_date`)
      SUBPARTITION_EXPRESSION: month(`sale_date`)
        PARTITION_DESCRIPTION: 2025
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 0
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2025-11-09 16:34:32
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
                    TINDEX_ID: 10037
     TINDEX_ID_STORAGE_FORMAT: 00002735
              DATA_SPACE_TYPE: DATA_SPACE_TYPE_USER
              SE_PRIVATE_DATA: create_data_obj_task_id=29572817330634866;distribution_policy_id=1;hidden_pk_autoinc_tindex_id=10038;schema_status=0;
         TABLE_SCHEMA_VERSION: 1
          TABLE_SCHEMA_STATUS: 0
             TABLE_EXTRA_INFO: {"version":3,"create_ts":0}
        TABLE_SE_PRIVATE_DATA: autoinc_version=1;create_data_obj_task_id=29572817330634866;distribution_policy_id=1;origin_db=test;origin_table=sales;partition_policy_id=0;sync_table=0;
*************************** 6. row ***************************
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test
                   TABLE_NAME: sales
               PARTITION_NAME: p1
            SUBPARTITION_NAME: p1sp1
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: 2
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: HASH
         PARTITION_EXPRESSION: year(`sale_date`)
      SUBPARTITION_EXPRESSION: month(`sale_date`)
        PARTITION_DESCRIPTION: 2025
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 0
              MAX_DATA_LENGTH: 0
                 INDEX_LENGTH: 0
                    DATA_FREE: 0
                  CREATE_TIME: 2025-11-09 16:34:32
                  UPDATE_TIME: NULL
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT: 
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
                    TINDEX_ID: 10039
     TINDEX_ID_STORAGE_FORMAT: 00002737
              DATA_SPACE_TYPE: DATA_SPACE_TYPE_USER
              SE_PRIVATE_DATA: create_data_obj_task_id=29572817330634866;distribution_policy_id=1;hidden_pk_autoinc_tindex_id=10040;schema_status=0;
         TABLE_SCHEMA_VERSION: 1
          TABLE_SCHEMA_STATUS: 0
             TABLE_EXTRA_INFO: {"version":3,"create_ts":0}
        TABLE_SE_PRIVATE_DATA: autoinc_version=1;create_data_obj_task_id=29572817330634866;distribution_policy_id=1;origin_db=test;origin_table=sales;partition_policy_id=0;sync_table=0;
6 rows in set (0.05 sec)
中国站
目录