MySQL statement method to check the space occupied by each database (with code)

The content of this article is about the method of checking the space occupied by each database using MySQL statements (with code). It has certain reference value. Friends in need can refer to it. I hope it will help You helped.

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 the database How much disk size is occupied? You can query the capacity of the entire database through some SQL statements, or you can view the capacity occupied by the table individually. (Recommended course: MySQL Video Tutorial)

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='数据库名';

Above The result obtained is in bytes and can be passed from 24 to M.

2. Query the size of all data

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables; -- 查询所有的数据大小

3. Query the data 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, and this database is installed with mysql Metadata, including 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:

schemataTable: This table mainly contains information about all databases stored in mysql

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

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

statisticsTable: stores index information in the table.

user_privilegesTable: stores user permission information.

schema_privilegesTable: Stores database permissions.

table_privilegesTable: stores the permissions of the table.

column_privilegesTable: stores column permission information.

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

collationsTable: Provides comparison information for each character set.

collation_character_set_applicabilityTable: 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_constraintsTable: This table is mainly used to record the tables and constraint types that describe the constraints.

key_column_usageTable: Record columns with constraints.

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

viewsTable: records view information and requires show view permission.

triggersTable: stores trigger information and requires super permission.

The above is the detailed content of MySQL statement method to check the space occupied by each database (with code). For more information, please follow other related articles on the PHP Chinese website!