MySQL 查询数据库/表的容量、结构

选择数据库

use information_schema

1、查看所有数据库的容量

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

2、查看所有库各表的容量

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

3、查看指定数据库的容量

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='数据库名';

4、查看指定数据库、表的容量

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='数据库名' and table_name='表名'
order by data_length desc, index_length desc;

5、显示表结构

desc table_name;    -- 查看表整体结构
describe table_name;     -- 用于查看表整体结构
show columns from table_name;    -- 用于查看表整体结构
show create table table_name;    -- 用于查看表整体结构
show full fields from table_name;    -- 用于查看表整体结构
show fields from table_name;    -- 用于查看表整体结构
desc table_name field_name;    -- 用于查询表中的一部分
show index from table_name;    -- 用于查看表局部结构（这种显示不是很直观，也不是可以完全显示所有信息）
explain table_name;    -- 用于查看表整体结构

6、数据库表结构详细查询

select * from information_schema.columns
where table_schema = '数据库名' and table_name = '表名';

7、查看表生成的DDL

show create table table_name;