我要评论查看某个数据库中所有表的空间与行数统计
select
table_name as `表名`,
table_rows as `行数`,
round(data_length / 1024 / 1024, 2) as `数据大小(mb)`,
round(index_length / 1024 / 1024, 2) as `索引大小(mb)`,
round((data_length + index_length) / 1024 / 1024, 2) as `总占用空间(mb)`,
round((data_length + index_length) / 1024 / 1024 / 1024, 2) as `总占用空间(gb)`
from
information_schema.tables
where
table_schema = 'your_database_name' -- 替换为你的数据库名
order by
(data_length + index_length) desc;执行结果
查看某个数据库总空间占用情况
select
table_schema as `数据库名`,
sum(table_rows) as `总行数`,
round(sum(data_length) / 1024 / 1024, 2) as `数据总大小(mb)`,
round(sum(index_length) / 1024 / 1024, 2) as `索引总大小(mb)`,
round(sum(data_length + index_length) / 1024 / 1024, 2) as `总占用空间(mb)`,
round(sum(data_length + index_length) / 1024 / 1024 / 1024, 2) as `总占用空间(gb)`
from
information_schema.tables
where
table_schema = 'your_database_name'
group by
table_schema;执行结果
知识扩展
统计数据库下所有表的数据行数
刚开始统计数据库中有多少行数据的时候，使用 information_schema.tables 去统计发现误差很大 ，具体原因这里不再说明
select table_schema as '数据库', table_name as '表名', table_comment 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='test' order by table_rows desc, index_length desc;
以下是另一个方法：
执行sql，生成所有表的统计语句
select concat(
'select "',
table_name,
'", count(*) as t from `',
table_schema,
'`.`',
table_name,
'` union all'
) from information_schema.tables
-- 排除指定库
where
table_schema not in('mysql','information_schema','performance_schema','sys');
结果如下：
查询出来的结果拷贝出来，去掉最后一行的 union all
汇总
到此这篇关于mysql数据库实现统计所有表的空间占用与行数的文章就介绍到这了,更多相关mysql统计表空间占用与行数内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论