【MySQL学生手册】INFORMATION_SCHEMA数据库

本文地址:https://www.askmac.cn/archives/mysql-information-schema.html

 

 

INFORMATION_SCHEMA数据库作为MySQL数据库元数据的一个中央集中仓库存在。它是一个“虚拟的数据库”,因为它不存放在磁盘任何位置。但它和其他数据库一样含有表,且表中的内容可以通过使用select语句和其它数据库一样查询访问。此外,你还可以使用select来获取关于INFORMATION_SCHEMA其本身的信息,如可以使用以下语句来列出此数据库下所有的表名:

 

 

 

注意:随着所MySQL所使用的版本不同,查询结果可能有所差异,更多详细信息请查阅最新版本的在线MySQL参考手册。

 

INFORMATION_SCHEMA下的表

在INFORMATION_SCHEMA数据库中,这些表包含了以下类别的信息:

  • CHARACTER_SETS — 关于字符集信息
  • COLLATIONS — 关于每个字符集的(排序)规则信息
  • COLLATION_CHARACTER_SET_APPLICABILITY — 关于字符集和每种(排序)规则的对应关系信息
  • COLUMNS — 关于表中的列信息
  • COLUMN_PRIVILEGES — 关于MySQL对应账号所有列权限信息
  • ENGINES — 关于存储引擎的信息
  • EVENTS — 关于计划事件信息
  • FILES — 从7.8版本(未使用MySQL Cluster版本的情况下)开始,此表会显示关于InnoDB数据文件信息。早前的版本中此表都显示的是关于NDB数据文件的存储信息。
  • GLOBAL_STATUS — 关于服务端状态信息,7.6版本之后此表开始将被存放在PERFORMANCE_SCHEMA数据库中
  • GLOBAL_VARIABLES — 关于服务端全局变量信息,7.6版本之后此表开始将被存放在PERFORMANCE_SCHEMA数据库中
  • KEY_COLUMN_USAGE — 关于含有约束的键值列信息
  • OPTIMIZER_TRACE — 关于内部优化器执行语句的追踪信息,启用相应追踪功能需要启动设置optimizer_trace变量
  • PARAMETERS — 提供关于存储过程和功能函数所使用的参数及功能函数返回值信息
  • PLUGINS — 关于服务端插件信息
  • PROCESSLIST — 关于正在运行的线程信息
  • REFERENTIAL_CONSTRAINTS — 关于外键信息
  • ROUTINES — 关于存储过程和功能函数的相关信息
  • SCHEMATA — 关于数据库的信息
  • SCHEMA_PRIVILEGES — 关于MySQL用户账号所有的数据库权限
  • SESSION_STATUS — 关于当前连接的状态信息,7.6版本之后此表开始将被存放在PERFORMANCE_SCHEMA数据库中
  • SESSION_VARIABLES — 关于当前连接的会话变量信息,7.6版本之后此表开始将被存放在PERFORMANCE_SCHEMA数据库中
  • STATISTICS – 关于表索引的统计信息
  • TABLES — 关于数据库中表的信息
  • TABLESPACES — 提供了活动的表空间的信息,注意此表不提供关于InnoDB表空间的元数据信息,你需要去INNODB_SYS_TABLESPACES和INNODB_SYS_DATAFILES下查看,当然MySQL 5.7.8以后,FILES表也提供了InnoDB表空间相关的元数据信息。
  • TABLE_CONSTRAINTS — 关于表上的约束信息
  • TABLE_PRIVILEGES — 关于由MySQL用户账号所有的对应表权限信息
  • TRIGGERS — 关于数据库中触发器信息
  • USER_PRIVILEGES — 关于MySQL用户账号所有的全局性的权限信息
  • VIEWS — 关于数据库中视图信息
  • INNODB_XXXX表 — 这些表被用于监控正在进行的InnoDB活动,可以用于在出现问题前探测到这些低效的性能及承载问题。

 

INFORMATION_SCHEMA表的查看

为了显示INFORMATION_SCHEMA的表中的列名信息,你可以使用以下查询语句,在table_name中指定你需要查看的表:

 

对于INFORMATION_SCHEMA数据库的库名,它下面表的表名,以及列名都不是大小写敏感的,因此,如果查询语句如下:

 

mysql> select column_name from information_schema.columns
     -> where table_schema='information_schema'
     -> and table_name='views';

 

其结果和上图是一样的。

 

当你通过使用select语句从information_schema库中获取元数据,你就可以利用到select语句所能使用的特性:

  • 限定你需要获取的列
  • 使用where条件语法来限定哪些行信息是你需要的
  • 可以使用group by或order by对结果集进行分组排序
  • 可以使用表连接,合并及子查询。
  • 你可以通过对INFORMATION_SCHEMA库表的查询语句,将其和create table … select或insert … select一起使用。这样可以保存结果集,并在之后用于其他语句的使用。

 

以下示例展示了如何使用各种select语句来从information_schema库中拉取相应信息:

  • 显示world数据库中的表所使用的存储引擎
  • 找到所有包含数据类型SET的列的表:
  • 显示每个字符集和默认排序规则:
  • 显示每个数据库中表的数量:

 

information_schema是只读的,其中的表不能被insert, delete或update语句所修改。如果这样做,会报错并中止执行:

 

mysql> DELETE FROM INFORMATION_SCHEMA.VIEWS;
ERROR 1288 (HY000): The target table VIEWS of the DELETE is not updatable

 

以下为在现实工作中,你可能会用到information_schema的例子:

  • 查看每个数据库数据所占的大小:
  • 查看用户账号及其权限(确保每个用户都设置有密码)
  • 对每张表中的某种数据类型做一个统计

 

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号