information_schema使用说明 - xiaoboluo768/mysql-system-schema GitHub Wiki
- 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语句。无论使用哪种查询方式，都必须拥有某个对象的权限才能看到相关的数据。
- PS：
- 在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
root@localhost : information_schema 12:20:31> 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
root@localhost : information_schema 12:21:41> 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子句可单独使用，但要同时使用where与like子句时，like子句必须在where之后- 参考资料