查看mysql中database占用磁盘空间的大小SQL语句【测试通过】

迷南。 2024-04-19 11:18 44阅读 0赞

转载：[https://blog.csdn.net/xiaoyu19910321/article/details/78995736][https_blog.csdn.net_xiaoyu19910321_article_details_78995736]

一.mysql服务器下查看所有的数据库占用空间大小

1.登录mysql后切换到 information\_schema 库

use information_schema;

2.执行sql

select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,  
    concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size  
    from information_schema.tables  
    group by TABLE_SCHEMA  
    order by data_length desc;

3.查看结果

<table style="width:335px;"> 
 <tbody> 
  <tr> 
   <td>TABLE_SCHEMA</td> 
   <td>data_size</td> 
   <td>index_size</td> 
  </tr> 
  <tr> 
   <td>tpshop</td> 
   <td>7.65 MB</td> 
   <td>1.68MB</td> 
  </tr> 
  <tr> 
   <td>TTTAE</td> 
   <td>1.99 MB</td> 
   <td>0.27MB</td> 
  </tr> 
  <tr> 
   <td>wordpress</td> 
   <td>1.68 MB</td> 
   <td>0.42MB</td> 
  </tr> 
  <tr> 
   <td>we7</td> 
   <td>0.04 MB</td> 
   <td>0.16MB</td> 
  </tr> 
  <tr> 
   <td>wiki</td> 
   <td>1.15 MB</td> 
   <td>0.20MB</td> 
  </tr> 
  <tr> 
   <td>ultrax</td> 
   <td>1.60 MB</td> 
   <td>1.34MB</td> 
  </tr> 
  <tr> 
   <td>performance_schema</td> 
   <td>0.00 MB</td> 
   <td>0.00MB</td> 
  </tr> 
  <tr> 
   <td>mysql</td> 
   <td>0.85 MB</td> 
   <td>0.10MB</td> 
  </tr> 
  <tr> 
   <td>information_schema</td> 
   <td>0.00 MB</td> 
   <td>0.00MB</td> 
  </tr> 
 </tbody> 
</table>

二、查看整个数据库大小

1.登录mysql后切换到 information\_schema 库

use information_schema;

2.执行语句

select sum(data_length/1024/1024) as data from tables ;

三、查看单个数据库大小1.登录mysql后切换到 information\_schema 库

use information_schema;

2.执行语句【we7是想查询库名称】

select sum(data_length/1024/1024) as data from tables where table_schema='we7';

[https_blog.csdn.net_xiaoyu19910321_article_details_78995736]: https://blog.csdn.net/xiaoyu19910321/article/details/78995736