PARTITIONS table is a
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
PARTITIONS table 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
1 being 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
Subpartitions within a given partition are also indexed and
reindexed in the same manner as partitions are indexed within
PARTITION_METHOD: One of the values
LINEAR KEY; that
is, one of the available partitioning types as discussed in
Section 18.2, “Partitioning Types”.
SUBPARTITION_METHOD: One of the values
LINEAR KEY; that
is, one of the available subpartitioning types as discussed in
Section 18.2.6, “Subpartitioning”.
For example, consider a partitioned table created in the
test database using this statement:
CREATE TABLE tp ( c1 INT, c2 INT, c3 VARCHAR(25) ) PARTITION BY HASH(c1 + c2) PARTITIONS 4;
PARTITION_EXPRESSION column in a
PARTITIONS table record for a partition from this table
c1 + c2, as shown here:
SELECT DISTINCT PARTITION_EXPRESSION>
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_EXPRESSION does 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
partition, it contains the value set in the partition's
VALUES LESS THAN clause, which can be
either an integer or
MAXVALUE. For a
LIST partition, this column contains the
values defined in the partition's
clause, which is a comma-separated list of integer values.
For partitions whose
LIST, this column is always
TABLE_ROWS: The number of table rows in the
the row count given in the
column 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 divided by
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
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
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.
Some storage engines do not update this time; for tables
using these storage engines, this value 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
PARTITION_COMMENT column is also
1024, characters to match this limit (Bug #11748924, Bug
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 MySQL 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.
Currently, the value of this column is always
A nonpartitioned table has one record in
however, the values of the
PARTITION_DESCRIPTION columns are all
PARTITION_COMMENT column in this case is