10.1 什么是information_schema

information_schema提供了对数据库元数据、统计信息以及有关MySQL Server信息的访问（例如：数据库名或表名、字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典或系统目录。

在每个MySQL实例中都有一个独立的information_schema，用来存储MySQL实例中所有其他数据库的基本信息。information_schema库下包含多个只读表（非持久表），所以在磁盘中的数据目录下没有对应的关联文件，且不能对这些表设置触发器。虽然在查询时可以使用USE语句将默认数据库设置为information_schema，但该库下的所有表是只读的，不能执行INSERT、UPDATE、DELETE等数据变更操作。

针对information_schema下的表的查询操作可以替代一些SHOW查询语句（例如：SHOW DATABASES、SHOW TABLES等），与使用SHOW语句相比，通过查询information_schema下的表获取数据有以下优势：

● 它符合“Codd法则”，所有的访问都是基于表的访问完成的。

● 可以使用SELECT语句的SQL语法，只需要学习所要查询的一些表名和列名的含义即可。

● 基于SQL语句的查询，对来自information_schema中的查询结果可以进行过滤、排序、联结操作，查询的结果集格式对应用程序来说更友好。

● 这种技术实现与其他数据库系统中类似的实现更具互操作性。例如：Oracle数据库的用户熟悉查询Oracle数据字典中的表，那么在MySQL中也可以使用同样的方法来执行查询数据字典的表，获取想要的数据。

访问information_schema需要的权限如下：

● 所有用户都有访问information_schema下的表权限（但只能看到这些表中与用户具有访问权限的对象相对应的数据行），但只能访问Server层的部分数据字典表。Server层的部分数据字典表以及InnoDB层的数据字典表需要额外授权才能访问，如果用户权限不足，当查询Server层的数据字典表时将不会返回任何数据，或者对某个列没有权限访问时，该列返回NULL值；当查询InnoDB层的数据字典表时将直接拒绝访问（注意：要访问这些表需要有process权限，而不是select权限）。

● 从information_schema中查询相关数据需要的权限也适用于SHOW语句。无论使用哪种查询方式，都必须拥有访问某个对象的权限才能看到相关的数据。

提示：在MySQL 5.6版本中总共有59个表，其中有10个MyISAM引擎临时表（数据字典表）和49个Memory引擎临时表（保存统计信息和一些临时信息）。在MySQL 5.7版本中，该schema下总共有61个表，其中有10个InnoDB引擎临时表（数据字典表）和51个Memory引擎临时表。在MySQL 8.0版本中，该schema下的数据字典表（包含部分原Memory引擎临时表）都迁移到了mysql schema下，且在mysql schema下这些数据字典表被隐藏，无法直接访问，需要通过information_schema下的同名表进行访问（统计信息表被保留在information_schema下且仍然为Memory引擎）。

虽然直接通过查询information_schema中的表获取数据有众多优势，但是因为SHOW语法已经耳熟能详且被广泛使用，所以SHOW语句仍然是一个备选方法，且随着information_schema的实现，SHOW语句中的功能还有所增强（可以使用like或where子句进行过滤）。例如：

# 语法
Syntax:
SHOW [GLOBAL | SESSION] VARIABLES
    [LIKE 'pattern' | WHERE expr]
# 示例1
mysql> show variables like '%log_bin%';
+------------------------------------+--------------------------------------------------+
| Variable_name                     | Value                                           |
+------------------------------------+--------------------------------------------------+
| log_bin                           | ON                                              |
| log_bin_basename                  | /home/mysql/data/mysqldata1/binlog/mysql-bin     |
| log_bin_index                     | /home/mysql/data/mysqldata1/binlog/mysql-bin.index |
| log_bin_trust_function_creators    | ON                                              |
| log_bin_use_v1_row_events         | OFF                                             |
| sql_log_bin                       | ON                                              |
+------------------------------------+--------------------------------------------------+
6 rows in set（0.00 sec）
# 示例2
mysql> show variables where Variable_name like 'log_bin%' and Value='ON';
+---------------------------------+-------+
| Variable_name                  | Value |
+---------------------------------+-------+
| log_bin                        | ON    |
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
2 rows in set（0.00 sec）
# 注意，like与where子句可单独使用，但要同时使用时，like子句必须在where之后