在mysql中有一個默認(rèn)的數(shù)據(jù)表
information_schema，information_schema這張數(shù)據(jù)表保存了MySQL服務(wù)器所有數(shù)據(jù)庫的信息运褪。如數(shù)據(jù)庫名，數(shù)據(jù)庫的表，表欄的數(shù)據(jù)類型與訪問權(quán)限等陡蝇。再簡單點，這臺MySQL服務(wù)器上哮肚，到底有哪些數(shù)據(jù)庫登夫、各個數(shù)據(jù)庫有哪些表，每張表的字段類型是什么允趟，各個數(shù)據(jù)庫要什么權(quán)限才能訪問恼策，等等信息都保存在information_schema表里面，所以請勿刪改此表潮剪。
--1.切換數(shù)據(jù)庫
use information_schema
--2. 查看數(shù)據(jù)庫的大小
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'
查看所有數(shù)據(jù)庫容量大小
select
table_schema as '數(shù)據(jù)庫',
sum(table_rows) as '記錄數(shù)',
sum(truncate(data_length/1024/1024, 2)) as '數(shù)據(jù)容量(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;
查看所有數(shù)據(jù)庫各表容量大小
select
table_schema as '數(shù)據(jù)庫',
table_name as '表名',
table_rows as '記錄數(shù)',
truncate(data_length/1024/1024, 2) as '數(shù)據(jù)容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;
查看指定數(shù)據(jù)庫容量大小
select
table_schema as '數(shù)據(jù)庫',
sum(table_rows) as '記錄數(shù)',
sum(truncate(data_length/1024/1024, 2)) as '數(shù)據(jù)容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';
查看指定數(shù)據(jù)庫各表容量大小
select
table_schema as '數(shù)據(jù)庫',
table_name as '表名',
table_rows as '記錄數(shù)',
truncate(data_length/1024/1024, 2) as '數(shù)據(jù)容量(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;
