Mysql查看数据库和表的占用空间大小

查询所有数据库占用磁盘空间大小的SQL语句如下:

SELECT TABLE_SCHEMA AS '数据库名', concat(TRUNCATE(sum(data_length)/1024/1024,2),'MB') AS '数据大小',
concat(TRUNCATE(sum(index_length)/1024/1024,2),'MB') AS index_size
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
ORDER BY data_length DESC;

查询单个数据库里面各个表所占磁盘空间大小包括其索引的大小,SQL语句如下:

SELECT table_schema AS '数据库',table_name AS '表名',table_rows AS '记录数',TRUNCATE (data_length / 1024 / 1024, 2) AS '数据容量(MB)',TRUNCATE (index_length / 1024 / 1024, 2) AS '索引容量(MB)'
FROM information_schema. TABLES
WHERE TABLE_SCHEMA = '要查的数据库名'
ORDER BY table_rows DESC;

Mysql 查看数据库中所有表的记录数:

USE information_schema; 
SELECT table_name AS '表名',table_rows AS '记录数' FROM TABLES 
WHERE TABLE_SCHEMA = '要查的数据库名'
ORDER BY table_rows DESC;

 

 

 

 

相关推荐