Shulou(Shulou.com)05/31 Report--

This article mainly explains "mysql how to query partition table information", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "mysql how to query partition table information" bar!

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, the partition method, the records of the data in the partition.

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

This paper introduces several methods to obtain the information of MySQL partition table.

1. Show create table table name

You can view the create statement that creates the partitioned table.

/ *!. * / is a special comment, which is certainly not implemented by other database products. Mysql special treatment will be carried out selectively. It can be considered 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 partition table.

3. View the information_ schema.partitions table

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSIONFROM information_schema.PARTITIONSWHERE TABLE_SCHEMA=SCHEMA () AND TABLE_NAME='TABLE_NAME'

You can see which partitions the table has, the method of the partition, the number of records of the data in the partition, and even the information of the sub-partition.

4. Explain select or explain partitions select statement

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

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

The output of the above two statements is the same.

Thank you for reading, the above is the content of "how to query partition table information in mysql". After the study of this article, I believe you have a deeper understanding of how to query partition table information in mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.