PARTITIONStable is a nonstandard table.
Each record in this table corresponds to an individual partition or subpartition of a partitioned table.
TABLE_CATALOG: This column is always
TABLE_SCHEMA: This column contains the name of the database to which the table belongs.
TABLE_NAME: This column contains the name of the table containing the partition.
PARTITION_NAME: The name of the partition.
SUBPARTITION_NAME: If the
PARTITIONStable record represents a subpartition, then this column contains the name of subpartition; otherwise it is
PARTITION_ORDINAL_POSITION: All partitions are indexed in the same order as they are defined, with
1being the number assigned to the first partition. The indexing can change as partitions are added, dropped, and reorganized; the number shown is this column reflects the current order, taking into account any indexing changes.
SUBPARTITION_ORDINAL_POSITION: Subpartitions within a given partition are also indexed and reindexed in the same manner as partitions are indexed within a table.
PARTITION_METHOD: One of the values
LINEAR KEY; that is, one of the available partitioning types as discussed in Section 22.2, “Partitioning Types”.
SUBPARTITION_METHOD: One of the values
LINEAR KEY; that is, one of the available subpartitioning types as discussed in Section 22.2.6, “Subpartitioning”.
For example, consider a partitioned table created in the
testdatabase using this statement:
CREATE TABLE tp ( c1 INT, c2 INT, c3 VARCHAR(25) ) PARTITION BY HASH(c1 + c2) PARTITIONS 4;
PARTITION_EXPRESSIONcolumn in a PARTITIONS table record for a partition from this table displays
c1 + c2, as shown here:
mysql> SELECT DISTINCT PARTITION_EXPRESSION > FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_NAME='tp' AND TABLE_SCHEMA='test'; +----------------------+ | PARTITION_EXPRESSION | +----------------------+ | c1 + c2 | +----------------------+ 1 row in set (0.09 sec)
SUBPARTITION_EXPRESSION: This works in the same fashion for the subpartitioning expression that defines the subpartitioning for a table as
PARTITION_EXPRESSIONdoes for the partitioning expression used to define a table's partitioning.
If the table has no subpartitions, then this column is
PARTITION_DESCRIPTION: This column is used for RANGE and LIST partitions. For a
RANGEpartition, it contains the value set in the partition's
VALUES LESS THANclause, which can be either an integer or
MAXVALUE. For a
LISTpartition, this column contains the values defined in the partition's
VALUES INclause, which is a comma-separated list of integer values.
For partitions whose
PARTITION_METHODis other than
LIST, this column is always
TABLE_ROWS: The number of table rows in the partition.
InnoDBtables, the row count given in the
TABLE_ROWScolumn is only an estimated value used in SQL optimization, and may not always be exact.
AVG_ROW_LENGTH: The average length of the rows stored in this partition or subpartition, in bytes.
This is the same as
DATA_LENGTH: The total length of all rows stored in this partition or subpartition, in bytes—that is, the total number of bytes stored in the partition or subpartition.
MAX_DATA_LENGTH: The maximum number of bytes that can be stored in this partition or subpartition.
INDEX_LENGTH: The length of the index file for this partition or subpartition, in bytes.
DATA_FREE: The number of bytes allocated to the partition or subpartition but not used.
CREATE_TIME: The time of the partition's or subpartition's creation.
UPDATE_TIME: The time that the partition or subpartition was last modified.
CHECK_TIME: The last time that the table to which this partition or subpartition belongs was checked.
InnoDBtables, this column is always
CHECKSUM: The checksum value, if any; otherwise, this column is
PARTITION_COMMENT: This column contains the text of any comment made for the partition.
In MySQL 5.7, the maximum length for a partition comment is defined as 1024 characters, and the display width of the
PARTITION_COMMENTcolumn is also 1024, characters to match this limit (Bug #11748924, Bug #37728).
The default value for this column is an empty string.
NODEGROUP: This is the nodegroup to which the partition belongs. This is relevant only to NDB Cluster tables; otherwise the value of this column is always
TABLESPACE_NAME: This column contains the name of the tablespace to which the partition belongs. The value of this column is always
A nonpartitioned table has one record in
INFORMATION_SCHEMA.PARTITIONS; however, the values of the
PARTITION_DESCRIPTIONcolumns are all
PARTITION_COMMENTcolumn in this case is blank.)