How to query the size of a table in mysql

In mysql, you can use the "concat(round(sum(DATA_LENGTH/1024/1024),2),'M')" statement with "where table_schema='database name' AND table_name='table name'" The size of the statement query table.

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

How to query the size of the table in mysql

Query the capacity of the entire database through sql statements, or view the capacity occupied by the table individually.

1. To query the capacity occupied by the table, just add the data and indexes of the table.

select sum(DATA_LENGTH)+sum(INDEX_LENGTH) from information_schema.tables where table_schema='数据库名称';

The results obtained above are in bytes. You can pass 24 in 24 results in units of M.

2. Query the size of all data

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables;

3. Query the data size of a certain table

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='数据库名称' AND table_name='表名称';

There is an information_schema database in mysql. This database is installed with MySQL metadata includes database information, table information in the database, etc. Therefore, if you want to query the disk space occupied by the database, you can operate on the information_schema database.

The tables in information_schema mainly include:

Schemata table: This table mainly contains the information of all databases stored in mysql

Tables table: This table stores Information about all tables in the database, including how many columns each table has.

Columns table: This table stores table field information in all tables.

Statistics table: stores index information in the table.

User_privileges table: stores user permission information.

Schema_privileges table: stores database permissions.

table_privileges table: stores the permissions of the table.

column_privileges table: stores column permission information.

Character_sets table: stores information about the character sets that can be used by mysql.

Collations table: Provides comparison information for each character set.

Collation_character_set_applicability table: It is equivalent to a comparison of the first two fields of the collations table and the character_sets table, recording the comparison information between character sets.

table_constraints table: This table is mainly used to record the tables and constraint types that describe the constraints.

　key_column_usage table: records columns with constraints.

Routines table: records information about stored procedures and functions, and does not contain customized procedure or function information.

Views table: records view information and requires show view permission.

The triggers table: stores trigger information and requires super permissions.

Recommended learning: mysql video tutorial

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