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

In this issue, the editor will bring you about how to check the capacity of database tables in MySQL. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Introduction to information_schema

In MySQL, think of information_schema as a database, or rather an information database. It holds information about all other databases maintained by the MySQL server. Such as the name of the database, the table of the database, the data type and access of the table column, etc.

SCHEMATA table: provides information about all databases in the current mysql instance.

TABLES table: provides information about the tables in the database (including views). Detailed description of a table belongs to which schema, table type, table engine, creation time and other information.

COLUMNS table: provides column information in the table. Details all the columns of a table and the information for each column.

STATISTICS table: provides information about table indexes.

The following is an example of using the information_ schema table to view database information:

View all database capacity sizes

Select

Table_schema as' database'

Sum (table_rows) as' number of records'

Sum (truncate (data_length/1024/1024, 2)) as' data capacity (MB)'

Sum (truncate (index_length/1024/1024, 2)) as' Index capacity (MB)'

From information_schema.tables

Group by table_schema

Order by sum (data_length) desc, sum (index_length) desc

View the capacity size of each table in all databases

Select

Table_schema as' database'

Table_name as' table name'

Table_rows as' number of records'

Truncate (data_length/1024/1024, 2) as' data capacity (MB)'

Truncate (index_length/1024/1024, 2) as' Index capacity (MB)'

From information_schema.tables

Order by data_length desc, index_length desc

View (specify library) mysql library capacity size

Select

Table_schema as' database'

Sum (table_rows) as' number of records'

Sum (truncate (data_length/1024/1024, 2)) as' data capacity (MB)'

Sum (truncate (index_length/1024/1024, 2)) as' Index capacity (MB)'

From information_schema.tables

Where table_schema='mysql'

View (specify library) the capacity size of each table in the mysql library

Select

Table_schema as' database'

Table_name as' table name'

Table_rows as' number of records'

Truncate (data_length/1024/1024, 2) as' data capacity (MB)'

Truncate (index_length/1024/1024, 2) as' Index capacity (MB)'

From information_schema.tables

Where table_schema='mysql'

Order by data_length desc, index_length desc

This is how to view the capacity of the database table in the MySQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.