tencent cloud

TDSQL Boundless

Release Notes
Product Introduction
Overview
Scenarios
Product Architecture
Instance Types
Compatibility Notes
Usage specification recommendations
Kernel Features
Kernel Overview
Kernel Version Release Notes
Functionality Features
Performance Features
Billing
Billing Overview
Purchase Method
Pricing Details
Renewal
Overdue Payments
Refund
Getting Started
Creating an Instance
Connect to Instances
User Guide
Data Migration
Data Subscription
Instance Management
Parameter Configuration
Account Management
Security Group
Backup and Restoration
Database Auditing
Tag Management
Use Cases
Technical Evolution and Usage Practices of Online DDL
Lock Mechanism Analysis and Troubleshooting Practices
Data Intelligent Scheduling and Related Practices for Performance Optimization
TDSQL Boundless Selection Guide and Practical Tutorial
Developer Guide
Developer Guide (MySQL Compatibility Mode)
Developer Guide (HBase Compatibility Mode)
Performance Tuning
Performance Tuning Overview
SQL Tuning
DDL Tuning
Performance White Paper
Performance Overview
TPC-C Test
Sysbench Test
API Documentation
History
Introduction
API Category
Making API Requests
Instance APIs
Security Group APIs
Task APIs
Backup APIs
Rollback APIs
Parameter APIs
Database APIs
Data Types
Error Codes
General Reference
System Architecture
SQL Reference
Database Parameter Description
TPC-H benchmark data model reference
Error Code Information
Security and Compliance
FAQs
Agreements
Service Level Agreement
Terms of Service
Privacy Policy
Data Processing And Security Agreement
Contact Us
Glossary
문서TDSQL BoundlessGeneral ReferenceSQL ReferenceData DictionaryINFORMATION_SCHEMAPARTITIONS_VERBOSE

PARTITIONS_VERBOSE

PDF
포커스 모드
폰트 크기
마지막 업데이트 시간: 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)
이전 주제: PARTITIONS다음 주제: PERSIST

도움말 및 지원

문제 해결에 도움이 되었나요?

피드백