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 19.2, “Partitioning Types”.
SUBPARTITION_METHOD: One of the values
LINEAR KEY; that
is, one of the available subpartitioning types as discussed in
Section 19.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
Prior to MySQL 5.6.25, for partitioned
InnoDB tables, this column was
NULL. The correct creation time is
shown in MySQL 5.6.25 and later. (Bug #17299181, Bug #69990)
UPDATE_TIME: The time that the partition or
subpartition was last modified.
this column is always
CHECK_TIME: The last time that the table to
which this partition or subpartition belongs was checked.
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.
Prior to MySQL 5.6.6, the display width of this column was 80
characters, and partition comments which exceeded this length
were truncated to fit. As of MySQL 5.6.6, the maximum length
for a partition comment is defined as 1024 characters, and the
display width of the
column is increased to 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 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