Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.4Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 33.8Mb
EPUB - 8.5Mb
HTML Download (TGZ) - 8.3Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.2Mb
Eclipse Doc Plugin (TGZ) - 9.2Mb
Eclipse Doc Plugin (Zip) - 11.3Mb
Man Pages (TGZ) - 198.4Kb
Man Pages (Zip) - 302.4Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  INFORMATION_SCHEMA Tables  /  The INFORMATION_SCHEMA PARTITIONS Table

22.15 The INFORMATION_SCHEMA PARTITIONS Table

The PARTITIONS table provides information about table partitions. See Chapter 20, Partitioning, for more information about partitioning tables.

INFORMATION_SCHEMA NameSHOW NameRemarks
TABLE_CATALOG MySQL extension
TABLE_SCHEMA MySQL extension
TABLE_NAME MySQL extension
PARTITION_NAME MySQL extension
SUBPARTITION_NAME MySQL extension
PARTITION_ORDINAL_POSITION MySQL extension
SUBPARTITION_ORDINAL_POSITION MySQL extension
PARTITION_METHOD MySQL extension
SUBPARTITION_METHOD MySQL extension
PARTITION_EXPRESSION MySQL extension
SUBPARTITION_EXPRESSION MySQL extension
PARTITION_DESCRIPTION MySQL extension
TABLE_ROWS MySQL extension
AVG_ROW_LENGTH MySQL extension
DATA_LENGTH MySQL extension
MAX_DATA_LENGTH MySQL extension
INDEX_LENGTH MySQL extension
DATA_FREE MySQL extension
CREATE_TIME MySQL extension
UPDATE_TIME MySQL extension
CHECK_TIME MySQL extension
CHECKSUM MySQL extension
PARTITION_COMMENT MySQL extension
NODEGROUP MySQL extension
TABLESPACE_NAME MySQL extension

Notes:

  • The PARTITIONS table 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 def.

  • 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 NULL.

  • 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 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 RANGE, LIST, HASH, LINEAR HASH, KEY, or LINEAR KEY; that is, one of the available partitioning types as discussed in Section 20.2, “Partitioning Types”.

  • SUBPARTITION_METHOD: One of the values HASH, LINEAR HASH, KEY, or LINEAR KEY; that is, one of the available subpartitioning types as discussed in Section 20.2.6, “Subpartitioning”.

  • PARTITION_EXPRESSION: This is the expression for the partitioning function used in the CREATE TABLE or ALTER TABLE statement that created the table's current partitioning scheme.

    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;
    

    The PARTITION_EXPRESSION column 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_EXPRESSION does for the partitioning expression used to define a table's partitioning.

    If the table has no subpartitions, then this column is NULL.

  • PARTITION_DESCRIPTION: This column is used for RANGE and LIST partitions. For a RANGE 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 VALUES IN clause, which is a comma-separated list of integer values.

    For partitions whose PARTITION_METHOD is other than RANGE or LIST, this column is always NULL.

  • TABLE_ROWS: The number of table rows in the partition.

    For partitioned InnoDB tables, the row count given in the TABLE_ROWS 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 TABLE_ROWS.

  • 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.

    Prior to MySQL 5.7.8, for partitioned InnoDB tables, this column was always NULL. The correct creation time is shown in MySQL 5.7.8 and later. (Bug #17299181, Bug #69990)

  • UPDATE_TIME: The time that the partition or subpartition was last modified.

    Prior to MySQL 5.7.8, for partitioned InnoDB tables, this column was always NULL. The correct modification time is shown in MySQL 5.7.8 and later. (Bug #17299181, Bug #69990)

  • CHECK_TIME: The last time that the table to which this partition or subpartition belongs was checked.

    For partitioned InnoDB tables, this column is always NULL.

  • CHECKSUM: The checksum value, if any; otherwise, this column is NULL.

  • 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_COMMENT column 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 MySQL Cluster tables; otherwise the value of this column is always 0.

  • TABLESPACE_NAME: This column contains the name of the tablespace to which the partition belongs. The value of this column is always DEFAULT.

  • A nonpartitioned table has one record in INFORMATION_SCHEMA.PARTITIONS; however, the values of the PARTITION_NAME, SUBPARTITION_NAME, PARTITION_ORDINAL_POSITION, SUBPARTITION_ORDINAL_POSITION, PARTITION_METHOD, SUBPARTITION_METHOD, PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, and PARTITION_DESCRIPTION columns are all NULL. (The PARTITION_COMMENT column in this case is blank.)


User Comments
Sign Up Login You must be logged in to post a comment.