查看mysql中数据库或表所占空间的大小

目录

[TOC]

1.计算数据库的总大小

修改your_database_name为实际数据库名字即可

SELECT 
    table_schema AS `Database`,
    SUM(data_length + index_length) / 1024 / 1024 AS `Size (MB)`
FROM 
    information_schema.tables 
WHERE 
    table_schema = 'your_database_name'
GROUP BY 
    table_schema;

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 892
Server version: 8.3.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> SELECT 
    ->     table_schema AS `Database`,
    ->     SUM(data_length + index_length) / 1024 / 1024 AS `Size (MB)`
    -> FROM 
    ->     information_schema.tables 
    -> WHERE 
    ->     table_schema = 'test'
    -> GROUP BY 
    ->     table_schema;
+------------------+---------------+
| Database         | Size (MB)     |
+------------------+---------------+
| test             | 2141.40625000 |
+------------------+---------------+
1 row in set (0.01 sec)

mysql> 

2.分别计算数据库中每个表的大小

修改your_database_name即可

SELECT 
    table_name AS `Table`,
    round(((data_length + index_length) / 1024 / 1024), 2) AS `Size (MB)`
FROM 
    information_schema.tables 
WHERE 
    table_schema = 'your_database_name'
ORDER BY 
    (data_length + index_length) DESC;

3.计算某个数据库中某个表的大小

修改your_database_name, your_table_name

SELECT table_name AS "your_table_name", 
round(((data_length + index_length) / 1024 / 1024), 2) AS "大小(MB)"
FROM information_schema.tables 
WHERE table_schema = 'your_database_name' 
AND table_name = 'your_table_name';