How to query partition table information in mysql

Query method: 1. Use the "show create table table name" statement; 2. Use the "show table table name" statement; 3. Query the "information_schema.partitions" table to see which partitions the table has. , partitioning method, and recording of data in the partition.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Introduces several methods to obtain MySQL partition table information.

1. show create table table name

You can view the create of the partition table statement.

/*!...*/ is a special comment that other database products will not execute. MySQL special processing will be executed selectively. It can be thought of as: conditional compilation in precompilation. Pay special attention to 50100, which indicates version 5.01.00 or higher before execution.

2. show table status

You can check whether the table is a partitioned table.

3. Check the information_schema.partitions table

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,
TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='TABLE_NAME';

to see which partitions the table has. Information such as the partition method, the number of data records in the partition, and even information about sub-partitions.

4. explain select or explain partitions select statement

Use this statement to display which partitions are scanned , and how they are used.

explain partitions select * from tb_sub2 where purchased='1989-01-01';
explain select * from tb_sub2 where purchased='1989-01-01';

The output results of the above two statements are the same.

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to query partition table information in mysql. For more information, please follow other related articles on the PHP Chinese website!