前言
在mysql中有一个默认的数据表information_schema,information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权限等。
再简单点,这台MySQL服务器上,到底有哪些数据库、各个数据库有哪些表,每张表的字段类型是什么,各个数据库要什么权限才能访问,等等信息都保存在information_schema表里面,所以请勿删改此表。
代码
1,切换数据库
- use information_schema;
2,查看数据库使用大小
- select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables where table_schema=’DB_Name’ ;
3,查看表使用大小
- select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables where table_schema=’DB_Name’ and table_name=’Table_Name’;
网上找的一个,亲测可用:
- mysql> use information_schema;
- Database changed
- mysql> select data_length,index_length
- -> from tables where
- -> table_schema='dbname'
- -> and table_name = 'tablename';
- +-------------+--------------+
- | data_length | index_length |
- +-------------+--------------+
- | 166379520 | 235782144 |
- +-------------+--------------+
- row in set (0.02 sec)
- mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,
- -> concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB
- -> from tables where
- -> table_schema='dbname'
- -> and table_name = 'tablename';
- +----------------+-----------------+
- | data_length_MB | index_length_MB |
- +----------------+-----------------+
- | 158.67MB | 224.86MB |
- +----------------+-----------------+
- row in set (0.03 sec)
1.查看所有数据库容量大小
- select
- table_schema as '数据库',
- sum(table_rows) as '记录数',
- sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
- sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
- from information_schema.tables
- group by table_schema
- order by sum(data_length) desc, sum(index_length) desc;
- ```
- ### 2.查看所有数据库各表容量大小
- ```sql
- select
- table_schema as '数据库',
- table_name as '表名',
- table_rows as '记录数',
- truncate(data_length/1024/1024, 2) as '数据容量(MB)',
- truncate(index_length/1024/1024, 2) as '索引容量(MB)'
- from information_schema.tables
- order by data_length desc, index_length desc;
3.查看指定数据库容量大小
例:查看mysql库容量大小
- select
- table_schema as '数据库',
- sum(table_rows) as '记录数',
- sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
- sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
- from information_schema.tables
- where table_schema='mysql';
4.查看指定数据库各表容量大小
例:查看mysql库各表容量大小
- select
- table_schema as '数据库',
- table_name as '表名',
- table_rows as '记录数',
- truncate(data_length/1024/1024, 2) as '数据容量(MB)',
- truncate(index_length/1024/1024, 2) as '索引容量(MB)'
- from information_schema.tables
- where table_schema='mysql'
- order by data_length desc, index_length desc;
- select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB
- from tables
- where table_schema='passport' and table_name='tb_user_info';
-- 569.98MB 141.98MB
- select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB
- from tables
- where table_schema='passport_v2' and table_name='tb_user_info';
-- 2128.94MB 285.00MB
本文转载自微信公众号「SQL数据库」,作者丶平凡世界 。转载本文请联系开发公众...
在Flash Player 10.1及以上版本中,adobe新增了全局错误处理程序UncaughtErrorEv...
CKeditor,以前叫FCKeditor,已经使用过好多年了,功能自然没的说。最近升级到3....
来源:DeepenStudy 漏洞文件:js.asp % Dimoblog setoblog=newclass_sys oblog.a...
本文实例讲述了AJAX+Servlet实现的数据处理显示功能。分享给大家供大家参考,具...
本文转载自微信公众号「SH的全栈笔记」,作者SH。转载本文请联系SH的全栈笔记公...
问题:我们在做flex的开发中,如果用到别人搭建好的框架,而别人的server名称往...
idea官方推送了2020.2.4版本的更新,那么大家最关心的问题来了,之前激活idea202...
大家好,我是狂聊君。 今天来聊一聊 Mysql 缓存池原理。 提纲附上,话不多说,直...
前言 项目开发中不管是前台还是后台都会遇到烦人的null,数据库表中字段允许空值...