MySQL库与表的数据量查询（库中有多少表、每个表有多少行、每个库的大小..等）

 

快速的统计一个表有多少列

SELECT COUNT(*) FROM
information_schema. COLUMNS
WHERE table_schema = 'syw_insert'   #syw_insert是库名
AND table_name = 'sbtest1';         #sbtest1是表名

每个数据库的大小

select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_size desc;

某个数据库中所有表的大小

（tpch_1t是库名）

select table_name,table_rows,data_length+index_length,concat(round((data_length+index_length)/1024/1024,2),'MB') data from tables where table_schema='tpch_1t';    

查看数据库中某个表的大小

select TABLE_SCHEMA,table_name,table_rows,data_length+index_length,

concat(round((data_length+index_length)/1024/1024,2),'MB')

data from tables where table_schema='syw_insert'     #syw_insert是库名

and table_name='sbtest1';   #sbtest1是表名

某个库下有多少张表

SELECT
table_name 
FROM
information_schema.TABLES 
WHERE
table_schema = 'syw_insert';     #syw_insert是库名

某个库下面所有表的行数

select table_name,table_rows from tables
where TABLE_SCHEMA = 'syw_insert'    #syw_insert是库名
order by table_rows desc;

查看库中有多少张表，库中的表总共有多少行，库的大小..

select TABLE_SCHEMA as '库名称', sum(table_rows) as '表行数(行）',count(concat(round((data_length+index_length)/1024/1024,2),'MB')) as '表数量(张)',
concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_size desc;