数据库的基本信息,都在这几张表里了

[删除(380066935@qq.com或微信通知)]

更好的阅读体验请查看原文:https://www.cnblogs.com/codeflyer/p/16015468.html

话说生产环境的数据库是不能本地直连的,所以公司一般都会提供一个比较简陋的数据库查询页面,在可控的范围内,支持你提交一些查询、变更SQL,满足你的查库功能。但是因为不能直接使用Navicat、DataGrip这些好用的数据库客户端,一些数据库的基本信息获取起来就比较麻烦了。比如你想看线上业务表都有哪些索引?是不是和测试环境不一致?这种情况应该怎么办呢?

其实好办!因为数据库的各种基础信息,都保存在INFORMATION_SCHEMA这个schema下了。INFORMATION_SCHEMA是MySQL自带的数据库,存储了MySQL中各个数据库的元数据。所以,只需要从INFORMATION_SCHEMA下的各个表里取数据,就可以获取到数据库的基本信息了

1、库信息

库信息存放在SCHEMATA表中,使用以下语句,即可查询全部的库信息。

select *
from INFORMATION_SCHEMA.SCHEMATA;

当然,你也可以用对应的show databases命令来获取库信息,但是显示的内容会更简洁些,只会展示数据库的名称。

2、表信息

表信息存放在TABLES表中,使用以下语句,即可查询全部的表信息。包括表名、数据量、自增值、行数等信息。

select *
from INFORMATION_SCHEMA.TABLES;

但是这么查询,会查出所有schema下的表信息,所以加上查询条件TABLE_SCHEMA = 'yourSchemaName'就可以了。

当然,这个表的查询也有对应的语句show tables from yourSchemaName。只不过这个语句也只会展示当前库下所有的表名,而且远没有直接从TABLES表里查询的数据详细。

3、列信息

列信息存放在COLUMNS表中,使用以下语句,即可查询指定表的列信息。包括列名、数据类型、长度、是否为空等你创建表时的基本信息。

select *
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'yourTableName';

对应的语句是show columns from yourTableName;

4、索引信息

索引信息存放在STATISTICS表中(很奇怪,为啥这个表不叫INDEX),使用以下语句,即可查询指定表的索引信息。包含了索引名、索引的字段等。

select *
from INFORMATION_SCHEMA.STATISTICS
where table_name = 'yourTableName';

对应的命令是show index from yourTableName;这个命令还是比较给力的,相比于前几个命令,它列出了很详细的索引信息。

优化后的SQL,建议收藏

好啦,其实INFORMATION_SCHEMA下的表还有很多,但是我认为平时最常用到的就是这四张表了。大家应该都已经知道了这四张表存放的信息和查询方式,这里我优化了查询SQL,让大家可以在查询的时候更直观~

-- 查表
select TABLE_NAME                 as 表名,
       ENGINE                     as 存储引擎,
       TABLE_ROWS                 as 行数,
       AVG_ROW_LENGTH             as 平均行大小,
       DATA_LENGTH / 1024 / 1024  as 表数据大小(MB),
       INDEX_LENGTH / 1024 / 1024 as 索引大小(MB),
       AUTO_INCREMENT             as 当前主键自增值,
       TABLE_COMMENT              as 表描述
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'yourSchemaName';

-- 查列
SELECT COLUMN_NAME              列名,
       COLUMN_TYPE              数据类型,
       DATA_TYPE                字段类型,
       CHARACTER_MAXIMUM_LENGTH 长度,
       IS_NULLABLE              是否为空,
       COLUMN_DEFAULT           默认值,
       COLUMN_COMMENT           备注
FROM INFORMATION_SCHEMA.COLUMNS
where table_name = 'yourTableName';

-- 查索引
select INDEX_NAME   as 索引名,
       COLUMN_NAME  as 索引中的字段名,
       SEQ_IN_INDEX as 索引中的顺序,
       INDEX_TYPE   as 索引类型
from INFORMATION_SCHEMA.STATISTICS
where table_name = 'yourTableName';