当前位置:首页 > 数据库教程 >

MySQL中查看某数据库的每个表的大小或某个表的大小

发布时间:2019-06-07 21:30:53 作者:佚名 阅读:(8)

在数据库维护的时候经常要查询数据库的大小和数据库中的单个表的大小,接下来吾爱编程为大家介绍一下MySQL中查看某数据库的每个表的大小或某个表的大小,有需要的小伙伴可以参考一下:

查询单个数据库大小:

SELECT table_schema "Database Name", SUM( data_length + index_length)/1024/1024
"Database Size (MB)" FROM information_schema.TABLES where table_schema = 'itbiancheng';
+-----------------+--------------------+
| Database Name   | Database Size (MB) |
+-----------------+--------------------+
| itbiancheng |        39.54687500 |
+-----------------+--------------------+
1 row in set (0.06 sec)

查询MySQL所有数据库的大小:

这个查询将在MySQL服务器计算所有数据库的大小。
SELECT table_schema "Database Name", SUM(data_length+index_length)/1024/1024
"Database Size (MB)"  FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+--------------------+
| Database Name      | Database Size (MB) |
+--------------------+--------------------+
| howyun             |        62.97689056 |
| information_schema |         0.00878906 |
| itbiancheng        |        47.92849827 |
| jingmey            |      9552.92950821 |
| mysql              |         0.67872334 |
| itbiancheng    |        39.54687500 |
| jingm        |      2728.33034515 |
| new_maque          |         0.25097656 |
| performance_schema |         0.00000000 |
+--------------------+--------------------+
9 rows in set (0.25 sec)

查询MySQL数据库单表大小:

计算出单个表的大小。请更改'itbiancheng'与实际的数据库名称和“news_list”与您的实际表名相同。

SELECT table_name "Table Name", table_rows "Rows Count", round(((data_length + index_length)/1024/1024),2)
"Table Size (MB)" FROM information_schema.TABLES WHERE table_schema = "itbiancheng" AND table_name ="news_list";
+--------------+------------+-----------------+
| news_list | Rows Count | Table Size (MB) |
+--------------+------------+-----------------+
| news_list |       7298 |           39.16 |
+--------------+------------+-----------------+
1 row in set (0.01 sec)

查询MySQL数据库的所有表的大小:

该查询将在MySQL服务器数据库中计算所有表的大小。请更改'itbiancheng'与实际的数据库名称相同。它还将列出每个表中的行数。
SELECT table_name "Table Name", table_rows "Rows Count", round(((data_length + index_length)/1024/1024),2)
"Table Size (MB)" FROM information_schema.TABLES WHERE table_schema = "new_itbiancheng" ;
+-----------------+------------+-----------------+
| Table Name      | Rows Count | Table Size (MB) |
+-----------------+------------+-----------------+
| banner_list  |          1 |            0.02 |
| link_list    |          0 |            0.02 
| news_list    |       4362 |           39.16 |
| user_list    |          0 |            0.03 |
+-----------------+------------+-----------------+
4 rows in set (0.01 sec)

欢迎分享转载→ MySQL中查看某数据库的每个表的大小或某个表的大小

© 2015-2019 - 吾爱编程网 版权所有 苏ICP备18033726号-1关于我们 - 网站声明 - 联系我们