How to query database capacity in mysql

Mysql method to query database capacity: 1. Open the DOS window, and then enter the bin directory of mysql; 2. Execute "SELECT table_schema AS 'shujuku', table_name AS 'biaoming', table_rows AS 'jilushu', TRUNCATE (data_length / 1024 / 1024, 2) ..." statement can be used to view the capacity of each table in all databases.

The operating environment of this tutorial: Windows 10 system, MySQL version 5.7, Dell G3 computer.

How to query the database capacity with mysql?

MySql View database and table capacity and sort

MySql View database and table capacity and sort View all database capacities

SELECT
    table_schema AS '数据库',
    sum(table_rows) AS '记录数',
    sum(
        TRUNCATE (data_length / 1024 / 1024, 2)
    ) AS '数据容量(MB)',
    sum(
        TRUNCATE (index_length / 1024 / 1024, 2)
    ) AS '索引容量(MB)'
FROM
    information_schema. TABLES
GROUP BY
    table_schema
ORDER BY
    sum(data_length) DESC,
    sum(index_length) DESC;

View all database table capacities

SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE (data_length / 1024 / 1024, 2) AS '数据容量(MB)',
    TRUNCATE (index_length / 1024 / 1024, 2) AS '索引容量(MB)'
FROM
    information_schema. TABLES
ORDER BY
    data_length DESC,
    index_length DESC;

View the capacity of the specified database

SELECT
    table_schema AS '数据库',
    sum(table_rows) AS '记录数',
    sum(
        TRUNCATE (data_length / 1024 / 1024, 2)
    ) AS '数据容量(MB)',
    sum(
        TRUNCATE (index_length / 1024 / 1024, 2)
    ) AS '索引容量(MB)'
FROM
    information_schema.tables where table_schema = 'your_table_name';

View the capacity of each table in the specified database

SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
    TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' 
FROM
    information_schema.TABLES 
WHERE
    table_schema = '指定的库名' 
ORDER BY
    data_length DESC,
    index_length DESC;

Recommended study: "MySQL Video Tutorial"

The above is the detailed content of How to query database capacity in mysql. For more information, please follow other related articles on the PHP Chinese website!