Note
This view is available starting with V1.4.
Purpose
The information_schema.PARTITIONS view displays partition information.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| TABLE_CATALOG | text | NO | Fixed value (def). |
| TABLE_SCHEMA | varchar(128) | NO | Database name. |
| TABLE_NAME | varchar(256) | NO | Table name. |
| PARTITION_NAME | varchar(64) | NO | Partition name. |
| SUBPARTITION_NAME | varchar(64) | NO | Subpartition name. |
| PARTITION_ORDINAL_POSITION | bigint(20) unsigned | NO | The position of the current partition among all partitions. |
| SUBPARTITION_ORDINAL_POSITION | bigint(20) unsigned | NO | The position of the current subpartition among all subpartitions. |
| PARTITION_METHOD | varchar(13) | NO | Partition type. |
| SUBPARTITION_METHOD | varchar(13) | NO | Subpartition type. |
| PARTITION_EXPRESSION | text | NO | The partitioning expression. |
| SUBPARTITION_EXPRESSION | text | NO | The subpartitioning expression. |
| PARTITION_DESCRIPTION | text | NO | The description for range and list partitions. |
| SUBPARTITION_DESCRIPTION | text | NO | The description for range and list subpartitions. |
| TABLE_ROWS | bigint(20) unsigned | NO | The number of rows in the partition. |
| AVG_ROW_LENGTH | bigint(21) unsigned | NO | The average row length stored in the partition or subpartition. |
| DATA_LENGTH | bigint(0) unsigned | NO | The total number of bytes stored in the partition or subpartition, which is the storage space occupied by the partition or subpartition, calculated in macroblock size, in bytes.
NoteFor V4.3.x, this field is no longer displayed as |
| MAX_DATA_LENGTH | bigint(0) unsigned | NO | This field is not supported. The default value is 0. |
| INDEX_LENGTH | bigint(0) unsigned | NO | The total length of indexes, which is the storage space occupied by indexes of the partition or subpartition, calculated in macroblock size, in bytes.
NoteFor V4.3.x, this field is no longer displayed as |
| DATA_FREE | bigint(0) unsigned | NO | This field is not supported. The default value is 0. |
| CREATE_TIME | timestamp(6) | NO | The creation time of the partition. |
| UPDATE_TIME | datetime | NO | This field is not supported. The default value is NULL. |
| CHECK_TIME | datetime | YES | This field is not supported. The default value is NULL. |
| CHECKSUM | bigint(0) | NO | This field is not supported. The default value is NULL. |
| PARTITION_COMMENT | text | NO | The comment of the partition. |
| NODEGROUP | varchar(256) | NO | The nodegroup to which the partition belongs. |
| TABLESPACE_NAME | varchar(268) | NO | The tablespace name to which the partition belongs. |
Sample query
Query the partition information of the t1 table.
obclient> select * from information_schema.partitions where table_name = 't1' limit 1\G
The query result is as follows:
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: t1
PARTITION_NAME: p0
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: HASH
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: a
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: NULL
SUBPARTITION_DESCRIPTION: NULL
TABLE_ROWS: 3000
AVG_ROW_LENGTH: 37
DATA_LENGTH: 0
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: NULL
CREATE_TIME: 2024-12-17 10:56:36.834215
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
1 row in set (0.020 sec)