【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的例子：

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