查询mysql数据库中表的行数与大小

1. 查询指定数据库中所有表的行数

select table_name,table_rows from information_schema.tables

where TABLE_SCHEMA = '数据库名'

order by table_rows desc;

1. 查询数据库中所有表的大小

select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size,

concat(truncate(index_length/1024/1024,2),' MB') as index_size

from information_schema.tables where TABLE_SCHEMA = '数据库名'

group by TABLE_NAME

order by data_length desc;

   1. 查询指定数据库中所有表的行数和占用空间大小及索引大小

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 data_length desc, index_length desc;

1. 查询所有数据库的大小

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_length desc;

1. 查询库中所有类似的表名

SELECT DISTINCT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'bbs\_c\_%' AND TABLE_SCHEMA='数据库名'