How to query data size in mysql

With the continuous development of data storage technology, the operating efficiency and data processing capabilities of the database have been greatly improved. However, in practical applications, we also need to monitor and manage the data in the database. One of the important indicators is data size. MySQL database is one of the most widely used relational databases at present, so what about in MySQL database? This article will introduce you to the specific solution to this problem.

1. Use the SHOW TABLE STATUS statement to query

The SHOW TABLE STATUS statement is a command in MySQL used to view the status of a table. It can query various information about the table. Among them, the Data_length and Index_length fields represent the data size and index size in bytes. When using this command to query, you can execute it as follows:

SHOW TABLE STATUS  
WHERE Name='table_name'\G

Among them, table_name indicates the name of the specific table to be queried.

After execution, the query results will be presented in a table, which contains various status information of the table, as shown in the figure below.

It should be noted here that the data size queried by this command is in bytes, not in MB or GB. Therefore, after using this command to query the data size, we need to convert the result unit into MB or GB to present it in a more intuitive way.

2. Use INFORMATION_SCHEMA query

The INFORMATION_SCHEMA database in the MySQL database contains information about all database objects. Therefore, we can use the table INFORMATION_SCHEMA.TABLES in this database to query the data size of the table.

The specific query method is as follows:

SELECT table_schema as '数据库名称', table_name as '表名称', round(((data_length + index_length) / 1024 / 1024), 2) as '表大小(MB)'
FROM information_schema.TABLES
WHERE table_schema = 'database_name' and table_name = 'table_name';

Among them, database_name and table_name respectively represent the names of the database and table that need to be queried.

After execution, the query results will present the data size in MB, as shown in the figure below.

It should be noted that the query statement of INFORMATION_SCHEMA is more complicated than the query statement of SHOW TABLE STATUS, but its query speed is faster and the size of multiple tables can be queried.

In practical applications, we can choose the appropriate query method according to needs to get the most accurate data size information. At the same time, you also need to pay attention to unit conversion when querying to avoid misunderstandings.

Summary

In the MySQL database, querying the data size is a relatively common operation, which can help us understand how much data is stored in the database so that we can better manage and control database resources. This article introduces two query methods, one is to query using the SHOW TABLE STATUS command, and the other is to query using the INFORMATION_SCHEMA data table. Both query methods have their own advantages and disadvantages, and they need to be selected according to specific circumstances when applying them.

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