mysql查询数据量

所有查询都需要进入到information_schema库进行操作

进入information_schema库命令

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

1、查询数据库数据总量

mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;
+-------------+
| data        |
+-------------+
| 114801.17MB |
+-------------+
1 row in set (0.09 sec)

2、查询某个数据库的数据量，此处为pay库

mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='pay';
+-----------+
| data      |
+-----------+
| 1695.94MB |
+-----------+
1 row in set (0.01 sec)

3、查询某个表的数据量，此处为pay数据库中的match表

        （1）.查询pay库中的所有表名

mysql> select table_name from information_schema.tables where table_schema='pay';
+------------------------------+
| table_name                   |
+------------------------------+
| avgTable                     |
| avgTable1                    |
| avgTable2                    |
| confirm_relation             |
| match                        |
+------------------------------+

        （2）.查看pay数据库中的match表

mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='pay' and table_name='match';
+--------+
| data   |
+--------+
| 0.02MB |
+--------+
1 row in set (0.01 sec)