tencent cloud

TDSQL Boundless

DocumentaçãoTDSQL BoundlessGeneral ReferenceSQL ReferenceData DictionaryINFORMATION_SCHEMAPARTITIONS_VERBOSE

PARTITIONS_VERBOSE

PDF
Modo Foco
Tamanho da Fonte
Última atualização: 2026-02-10 11:06:30

Function

INFORMATION_SCHEMA.PARTITIONS_VERBOSE is used to display more detailed information of the partitioned table than the standard PARTITIONS table. The main differences are:
PARTITIONS view: only displays actual information of the partitions/subpartitions; for tables with subpartitions, it does not display summary information of the primary partitions.
PARTITIONS_VERBOSE view: displays complete information of both primary partitions and subpartitions, providing a more comprehensive view of the partitioned structure.

Field Description

Field Name
Type
Description
TABLE_CATALOG
varchar(64)
The name of the catalog to which the table belongs, usually 'def'.
TABLE_SCHEMA
varchar(64)
The name of the database to which the table belongs.
TABLE_NAME
varchar(64)
Table name.
PARTITION_NAME
varchar(64)
Partition name.
SUBPARTITION_NAME
varchar(64)
If the row represents a subpartition (subpartition), it is the name of the subpartition; otherwise, it is NULL.
PARTITION_ORDINAL_POSITION
int unsigned
The ordinal position of the partition within the table.
SUBPARTITION_ORDINAL_POSITION
int unsigned
The ordinal position of the subpartition within the partition.
PARTITION_METHOD
varchar(13)
Partitioning method (RANGE, LIST, HASH, KEY, and so on)
SUBPARTITION_METHOD
varchar(13)
Subpartition method.
PARTITION_EXPRESSION
varchar(2048)
Partition expression.
SUBPARTITION_EXPRESSION
text
Subpartition expression.
PARTITION_DESCRIPTION
text
Partition description (such as boundary values for RANGE partitions).
TABLE_ROWS
bigint unsigned
Estimated number of rows in the partition.
AVG_ROW_LENGTH
bigint unsigned
Average row length in the partition.
DATA_LENGTH
bigint unsigned
Length of partition data (bytes).
MAX_DATA_LENGTH
bigint unsigned
Maximum length of partition data.
INDEX_LENGTH
bigint unsigned
Length of partition index.
DATA_FREE
bigint unsigned
Unused space in the partition.
CREATE_TIME
timestamp
Creation time of the partition.
UPDATE_TIME
datetime
Last update time of the partition.
CHECK_TIME
datetime
Last check time of the partition.
CHECKSUM
bigint
Checksum value of the partition.
PARTITION_COMMENT
text
Partition comment.
NODEGROUP
varchar(256)
Information of the node group.
TABLESPACE_NAME
varchar(268)
TABLESPACE NAME.
TINDEX_ID
int unsigned
TINDEX_ID of the table or partition.
TINDEX_ID_STORAGE_FORMAT
varchar(8)
Storage format of the table index ID.
DATA_SPACE_TYPE
varchar(128)
TYPE OF DATA SPACE.
SE_PRIVATE_DATA
mediumtext
PRIVATE DATA OF THE STORAGE ENGINE.
TABLE_SCHEMA_VERSION
int unsigned
Version Number of the Table Structure.
TABLE_SCHEMA_STATUS
smallint unsigned
Status of the Table Structure.
TABLE_EXTRA_INFO
mediumtext
Additional information of the table (JSON format).
TABLE_SE_PRIVATE_DATA
mediumtext
PRIVATE DATA OF THE TABLE STORAGE ENGINE.

Examples

1. Created a sales table with two-level partitioning:
First-level partitioning: Partitioned by year range (RANGE), with a total of 2 partitions (p0, p1).
Second-level partitioning: Partitioned by month using (HASH), with each first-level partition having 2 subpartitions.
2. Query the PARTITIONS_VERBOSE view: Returns 6 rows of data, including:
2 rows of first-level partition information (SUBPARTITION_NAME is NULL).
4 rows of information of the second-level partition.
# Create an example of a partitioned table
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)
);

# Query partition information
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)
Anterior: PARTITIONSPróximo: PERSIST

Ajuda e Suporte

Esta página foi útil?

comentários