Data size for MySQL query table

When we need to back up the data in the database, we need to know how much disk size the database occupies, the capacity of the entire database can be queried through some SQL statements, or the capacity of the table can be viewed separately.

1, to query the capacity of the table, that is, the table data and index add up on it can be

Select sum (data_length) + sum  from  where table_schema=' database name ';

The result obtained above is in bytes, which can be obtained by%1024 the result in%1024 to M.

2, query all the data size

Select concat (round(sum(data_length/1024x768/1024x768), 2),'M'from--  query all data size

3. Querying data for a table

Select concat (round(sum(data_length/1024x768/1024x768), 2),'M'fromwhere table_schema=and table_name=

There is a information_schema database in MySQL, which contains MySQL metadata, including database information, table information in the database, and so on. So to query the size of the database disk space can be

The INFORMATION_SCHEMA database has been manipulated.

The main tables in INFORMATION_SCHEMA are:

　　schemata table: This table is mostly stored in MySQL in all the database information

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

　　Columns table: This table stores information about table fields in all tables.

　　Statistics Table: Stores the information for the indexes in the table.

　　user_privileges Table: Stores the user's permission information.

　　schema_privileges Table: Database permissions are stored.

　　table_privileges Table: The permissions for the table are stored.

　　column_privileges Table: The permission information for the column is stored.

　　character_sets Table: stores information about the character set that MySQL can use.

　　Collations table: Provides cross-reference information for each character set.

　　collation_character_set_applicability Table: A comparison of the first two fields of the collations table and the Character_sets table, which records the control information between the character sets.

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

　　key_column_usage table: Records columns that have constraints.

　　Routines table: Records information about stored procedures and functions, and does not include custom procedures or function information.

　　 views Table: Record The view information, need to have show view permission.

　　triggers table: The information for the trigger is stored and Super permissions are required.

Data size for MySQL query table