Mysql对于information_schema库的一些用法

1) 查询mysql每一个数据库的大小

select table_schema,concat(sum((avg_row_length * table_rows + index_length))/1024/1024, "MB") from tables  group by table_schema;
# 排除information_schema,sys库
select table_schema,concat(sum((avg_row_length * table_rows + index_length))/1024/1024, "MB") from tables  where table_schema  not in ("information_schema", "sys")  group by table_schema;

2) 查询mysql单个库中每张表的的大小

select table_schema, table_name, concat((avg_row_length * table_rows + index_length)/1024/1024, "MB") from tables  where  table_schema = db_name group by table_name;

3) 查询mysql单个库中单个表的大小

select table_schema, table_name, concat((avg_row_length * table_rows + index_length)/1024/1024, "MB") from tables  where  table_schema = db_name and table_name = table_name;