怎么用navicat查看所有数据库各表容量大小

飞多多小程序

1. 查看所有数据库容量大小

1. select
   
2. table_schema as '数据库',
   
3. sum(table_rows) as '记录数',
   
4. sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
   
5. sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
   
6. from information_schema.tables
   
7. group by table_schema
   
8. order by sum(data_length) desc, sum(index_length) desc;

复制代码

注意：点击运行，如果出现错误：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '数据库' at line 1

解决办法：多次点击运行就行

2. 查看所有数据库各表容量大小

1. select
   
2. table_schema as '数据库',
   
3. table_name as '表名',
   
4. table_rows as '记录数',
   
5. truncate(data_length/1024/1024, 2) as '数据容量(MB)',
   
6. truncate(index_length/1024/1024, 2) as '索引容量(MB)'
   
7. from information_schema.tables
   
8. order by data_length desc, index_length desc;

复制代码

3. 查看指定数据库容量大小

例：查看mysql库容量大小

1. select
   
2. table_schema as '数据库',
   
3. sum(table_rows) as '记录数',
   
4. sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
   
5. sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
   
6. from information_schema.tables
   
7. where table_schema='mysql';

复制代码

4. 查看指定数据库各表容量大小

例：查看mysql库各表容量大小

1. select
   
2. table_schema as '数据库',
   
3. table_name as '表名',
   
4. table_rows as '记录数',
   
5. truncate(data_length/1024/1024, 2) as '数据容量(MB)',
   
6. truncate(index_length/1024/1024, 2) as '索引容量(MB)'
   
7. from information_schema.tables
   
8. where table_schema='mysql'
   
9. order by data_length desc, index_length desc;

复制代码