How to view the space occupied by data tables and the number of records in the MySQL database _ MySQL

This article describes how to view the space occupied by data tables and the number of records in the MySQL database. if you want to know the space occupied by each table in the MySQL database and the number of rows recorded in the table, you can open the information_schema database query of MySQL. This article describes the query method. if you want to know the space occupied by each table in the MySQL database and the number of table records, you can open the information_schema database of MySQL. There is a TABLES table in this database. The main fields of this table are:

TABLE_SCHEMA: database name
TABLE_NAME: table name
ENGINE: The storage ENGINE used
TABLES_ROWS: number of records
DATA_LENGTH: data size
INDEX_LENGTH: index size

For other fields, see the MySQL Manual. These fields are the most useful.
The size of the space occupied by a table is equivalent to the data size + index size,

Example:

1. to view the sizes of all tables in the hx database, use:

The code is as follows:

SELECT TABLE_NAME, DATA_LENGTH + INDEX_LENGTH, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'hx ';

+ ------------------- + -------------------------- + ------------ +
| TABLE_NAME | DATA_LENGTH + INDEX_LENGTH | TABLE_ROWS |
+ ------------------- + -------------------------- + ------------ +
| Enjoy_data | 6979584 | 70113 |
| Hx_record | 113410048 | 753279 |
| Itlearner_record | 21835546624 | 104917777 |
| Tmp_day_id | 17326 | 811 |
+ ------------------- + -------------------------- + ------------ +

2. to view the enjoy table size of the hx database, use:

The code is as follows:

SELECT DATA_LENGTH + INDEX_LENGTH, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'hx 'AND TABLE_NAME = 'enjoy _ data ';

Return value:

The code is as follows:

+ -------------------------- + ------------ +
| DATA_LENGTH + INDEX_LENGTH | TABLE_ROWS |
+ -------------------------- + ------------ +
| 6979584/70113 |
+ -------------------------- + ------------ +