MySQL常用命令

-- 查看表的行数
SELECT table_name,table_rows FROM information_schema.tables WHERE TABLE_SCHEMA = 'database_name' ORDER BY table_rows DESC;

-- 查看表的空间
select concat(round(sum(data_length/1024/1024/1024),2),'GB') as data from information_schema.tables where table_schema='database_name' and table_name = 'table_name';

-- 查看表的行数和空间
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='database_name'
order by data_length desc, index_length desc;

--查看分区表数据情况
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA='database_name'
AND TABLE_NAME='table_name';