-----------------------------------------------------------------------------------

前言：正在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’;

-----------------------------------------------------------------------------------

网上找的一个，亲测可用:

-----------------------------------------------------------------------------------

select table_schema as '数据库', sum(table_rows) as '记录数', sum(truncate(data_length/1024/1024, 2)) as '数据容质(MB)', sum(truncate(indeV_length/1024/1024, 2)) as '索引容质(MB)' from information_schema.tables group by table_schema order by sum(data_length) desc, sum(indeV_length) desc;

select table_schema as '数据库', table_name as '表名', table_rows as '记录数', truncate(data_length/1024/1024, 2) as '数据容质(MB)', truncate(indeV_length/1024/1024, 2) as '索引容质(MB)' from information_schema.tables order by data_length desc, indeV_length desc;

例：查察mysql库容质大小

select table_schema as '数据库', sum(table_rows) as '记录数', sum(truncate(data_length/1024/1024, 2)) as '数据容质(MB)', sum(truncate(indeV_length/1024/1024, 2)) as '索引容质(MB)' from information_schema.tables where table_schema='mysql';

例：查察mysql库各表容质大小

select table_schema as '数据库', table_name as '表名', table_rows as '记录数', truncate(data_length/1024/1024, 2) as '数据容质(MB)', truncate(indeV_length/1024/1024, 2) as '索引容质(MB)' from information_schema.tables where table_schema='mysql' order by data_length desc, indeV_length desc;

  -----------------------------------------------------------------------------------

select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, concat(round(sum(indeV_length/1024/1024),2),'MB') as indeV_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(indeV_length/1024/1024),2),'MB') as indeV_length_MB
from tables
where table_schema='passport_ZZZ2' and table_name='tb_user_info';

--   2128.94MB   285.00MB