mysql查看当前所有数据库中的表大小和元信息_查询数据库所有表的数据长度

查看所有mysql数据库表和索引大小

select table_schema,table_comment, 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;

或者
SELECT 
CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size, 
CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB') AS max_data_size, 
CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free, 
CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size, 
TABLE_SCHEMA 
FROM information_schema.tables 
group by TABLE_SCHEMA;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql查看当前某个数据库和数据库下所有的表的大小

select table_name,table_comment, 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 = 'face'
group by table_name
order by data_length desc;
1
2
3
4
5

mysql查看当前某个数据库和某个表的大小

select table_name,TABLE_COMMENT, 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 = 'face'
and table_name='kejiyuan';
1
2
3
4

查看表字段信息

select
COLUMN_NAME 字段名,
column_comment 字段说明,
column_type 字段类型,
column_key 约束 from information_schema.columns
where table_schema = 'crm'
and table_name = 'sys_post' ;
或
show full columns from  表名
1
2
3
4
5
6
7
8
9

columns信息

CREATE TEMPORARY TABLE `COLUMNS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COLUMN_DEFAULT` longtext,
  `IS_NULLABLE` varchar(3) NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
  `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
  `COLLATION_NAME` varchar(32) DEFAULT NULL,
  `COLUMN_TYPE` longtext NOT NULL,
  `COLUMN_KEY` varchar(3) NOT NULL DEFAULT '',
  `EXTRA` varchar(30) NOT NULL DEFAULT '',
  `PRIVILEGES` varchar(80) NOT NULL DEFAULT '',
  `COLUMN_COMMENT` varchar(1024) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

TABLES表信息

字段 含义
Table_catalog 数据表登记目录
Table_schema 数据表所属的数据库名
Table_name 表名称
Table_type 表类型[system view|base table]
Engine 使用的数据库引擎[MyISAM|CSV|InnoDB]
Version 版本，默认值10
Row_format 行格式[Compact|Dynamic|Fixed]
Table_rows 表里所存多少行数据
Avg_row_length 平均行长度
Data_length 数据长度
Max_data_length 最大数据长度
Index_length 索引长度
Data_free 空间碎片
Auto_increment 做自增主键的自动增量当前值
Create_time 表的创建时间
Update_time 表的更新时间
Check_time 表的检查时间
Table_collation 表的字符校验编码集
Checksum 校验和
Create_options 创建选项
Table_comment 表的注释、备注