table_schema:数据库名
table_name：表名
engine:所使用的存储引擎
table_rows:记录数
data_length:数据大小
index_length:索引大小
1、查看指定数据库实例的大小，比如数据库zabbix2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| monitor |
| mysql |
| test |
| wikidb |
| zabbix2 |
+--------------------+
6 rows in set (0.00 sec)
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select sum(data_length/1024/1024) as data from tables where table_schema='ZABBIX2';
+----------------+
| data |
+----------------+
| 57459.96875000 |
+----------------+
1 row in set (2.00 sec)
mysql> select round(sum(data_length/1024/1024),2) as data from tables where table_schema='ZABBIX2';
+----------+
| data |
+----------+
| 57462.97 |
+----------+
1 row in set (1.69 sec)
mysql> select concat(round(sum(data_length/1024/1024),2),'mb') as data from tables where table_schema='ZABBIX2';
+------------+
| data |
+------------+
| 57463.97mb |
+------------+
1 row in set (1.46 sec)
concat 连接函数 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。
2、查看指定数据库的表的大小，如数据库 zabbix2中的history_log表
mysql> select concat(round(sum(data_length/1024/1024),2),'mb') as data from tables
-> where table_schema='ZABBIX2' and table_name='history_log';
+--------+
| data |
+--------+
| 3.52mb |
+--------+
1 row in set (0.07 sec)
3、查看所有数据的大小
select concat(round(sum(data_length/1024/1024)2),'MB') as data from tables;
mysql> select sum(data_length/1024/1024) as data from tables;
+----------------+
| data |
+----------------+
| 57493.09162331 |
+----------------+
1 row in set (5.18 sec)
use information_schema
select concat(round(sum(data_length/1024/1024),2),'mb') as data from tables
where table_schema='a0117145552' and table_name='t_kd';