微信论坛

 找回密码
 立即注册
查看: 26651|回复: 0
打印 上一主题 下一主题

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

[复制链接]

主题

帖子

0

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
0
跳转到指定楼层
楼主
发表于 2021-3-30 15:16:13 | 只看该作者 |只看大图 回帖奖励 |倒序浏览 |阅读模式
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;
复制代码


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

注意:点击运行,如果出现错误: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;
复制代码




回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|微信人家论坛 ( 沪ICP备14052918号-4 )微信第三方平台沪公网安备 31011402006408号  

GMT+8, 2024-11-22 01:19 , Processed in 0.324902 second(s), 33 queries .

Powered by Discuz! X3.1

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表