How to query data size in mysql

1. Use the SHOW TABLE STATUS statement to query

The SHOW TABLE STATUS statement in MySQL can be used to view the status of the table and query its various information. Data_length and Index_length fields indicate the size of data and index 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.

Please note that the data size returned by this command is in bytes, not MB or GB. In order to display the results more clearly, we need to convert the unit of the result to MB or GB after using this command to query the data size.

2. Use INFORMATION_SCHEMA query

The information of all database objects is contained in the INFORMATION_SCHEMA database in the MySQL database. 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 order to obtain the most accurate data size information possible, we can use appropriate query methods for practical applications based on needs. At the same time, you also need to pay attention to unit conversion when querying to avoid misunderstandings.

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!