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

Today, I would like to share with you the relevant knowledge about how mysql queries the size of the table. The content is detailed and the logic is clear. I believe most people still know too much about this, so share this article for your reference. I hope you can get something after reading this article. Let's take a look at it.

In mysql, the "concat (round (sum (DATA_LENGTH/1024/1024), 2),'M')" statement can be used in conjunction with the "where table_schema=' database name 'AND table_name=' table name' statement to query the size of the table.

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

How does mysql query the size of the table

Query the capacity of the entire database through the sql statement, or view the capacity occupied by the table separately.

1. To query the capacity of the table, you can add up the data and index of the table.

Select sum (DATA_LENGTH) + sum (INDEX_LENGTH) from information_schema.tables where table_schema=' database name'

The results obtained above are in bytes and can be obtained from 24 to M in 24.

2. Query all data sizes

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

3. Query the data size of a table

Select concat (round (sum (DATA_LENGTH/1024/1024), 2),'M') from tables where table_schema=' database name 'AND table_name=' table name'

There is an information_schema database in mysql, which contains mysql metadata, including database information, table information in the database, and so on. So to query the amount of disk space occupied by the database, you can operate on the information_schema database.

The main tables in information_schema are:

Schemata table: this table mainly contains information about all the databases stored in mysql.

Tables table: this table stores information about all tables in the database, including information such as how many columns each table has.

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

Statistics table: stores information about indexes in the table.

User_ privileges table: stores the user's permission information.

Schema_ privileges table: database permissions are stored.

Table_ privileges table: stores the permissions of the table.

Column_ privileges table: stores permission information for columns.

Character_ sets table: stores information about the character sets that mysql can use.

Collations table: provides comparative information for each character set.

Collation_character_set_applicability table: 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 tables that describe tables and constraint types that have constraints.

Key_column_ use table: records columns with constraints.

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

Views table: the view information is recorded and show view permission is required.

Triggers table: stores the information of the trigger and requires super permission.

These are all the contents of the article "how to query the size of tables in mysql". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to 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.