Shulou(Shulou.com)06/01 Report--

Editor to share with you how the MySQL statement to view the space occupied by each database, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Select table_schema, sum (DATA_LENGTH) + sum (INDEX_LENGTH) from information_schema.tables group by table_schema

When we need to back up the data in the database, we need to know how much disk size the database occupies. We can query the capacity of the entire database through some sql statements, or we can view the capacity of the table separately. (recommended course: MySQL video tutorial)

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;-query all data sizes

3. Query the data 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 if you want to query the amount of disk space occupied by the database, you can use the

Perform operations 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 how the MySQL statement looks at the space occupied by each database. Thank you for your reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, 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.