Mysql查询数据库或数据表数据量大小

查询所有库的总大小

SELECT
	concat( round( sum( DATA_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS ALL_DATA,
	concat( round( sum( INDEX_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS ALL_INDEX,
	concat( round( sum(( DATA_LENGTH + INDEX_LENGTH )/ 1024 / 1024 ), 2 ), 'MB' ) AS TOTAL 
FROM
	information_schema.TABLES 

information_schema：Mysql自带的数据库，存放各类数据库相关信息的信息数据库，表多为视图
information_schema.tables：该数据库下的tables表
DATA_LENGTH：tables表下的一个字段，数据容量
INDEX_LENGTH：tables表下的一个字段，索引容量

查询指定数据库的大小

SELECT
	concat( round( sum( DATA_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS ALL_DATA,
	concat( round( sum( INDEX_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS ALL_INDEX,
	concat( round( sum(( DATA_LENGTH + INDEX_LENGTH )/ 1024 / 1024 ), 2 ), 'MB' ) AS TOTAL 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'test'

table_schema：tables表下的一个字段，数据库名称

统计每个数据库的大小

SELECT
	table_schema,
	concat( round( sum( DATA_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS ALL_DATA,
	concat( round( sum( INDEX_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS ALL_INDEX,
	concat( round( sum(( DATA_LENGTH + INDEX_LENGTH )/ 1024 / 1024 ), 2 ), 'MB' ) AS TOTAL 
FROM
	information_schema.TABLES 
GROUP BY
	table_schema;

查看指定数据库各表容量大小

SELECT
	table_schema,
	table_name AS 'name',
	table_rows AS 'rows',
	concat( round( DATA_LENGTH / 1024 / 1024, 2 ), 'MB' ) AS ALL_DATA,
	concat( round( INDEX_LENGTH / 1024 / 1024, 2 ), 'MB' ) AS ALL_INDEX,
	concat( round( ( DATA_LENGTH + INDEX_LENGTH )/ 1024 / 1024, 2 ), 'MB' ) AS TOTAL 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'test'