INFORMATION_SCHEMA 是每个 MySQL 实例中的一个数据库，用于存储 MySQL 服务器维护的所有其他数据库的信息。INFORMATION_SCHEMA 数据库包含几个只读表。它们实际上是视图，而不是基表，因此没有与之关联的文件，并且你不能在它们上面设置触发器。此外，没有名为该名称的数据库目录。INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

虽然你可以使用 USE 语句选择 INFORMATION_SCHEMA 作为默认数据库，但你只能读取表的内容，而不能对它们执行 INSERT、UPDATE 或 DELETE 操作。Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them.

以下是从 INFORMATION_SCHEMA 检索信息的语句示例：Here is an example of a statement that retrieves information from INFORMATION_SCHEMA:

mysql> SELECT table_name, table_type, engine
       FROM information_schema.tables
       WHERE table_schema = 'db5'
       ORDER BY table_name;
+------------+------------+--------+
| table_name | table_type | engine |
+------------+------------+--------+
| fk         | BASE TABLE | InnoDB |
| fk2        | BASE TABLE | InnoDB |
| goto       | BASE TABLE | MyISAM |
| into       | BASE TABLE | MyISAM |
| k          | BASE TABLE | MyISAM |
| kurs       | BASE TABLE | MyISAM |
| loop       | BASE TABLE | MyISAM |
| pk         | BASE TABLE | InnoDB |
| t          | BASE TABLE | MyISAM |
| t2         | BASE TABLE | MyISAM |
| t3         | BASE TABLE | MyISAM |
| t7         | BASE TABLE | MyISAM |
| tables     | BASE TABLE | MyISAM |
| v          | VIEW       | NULL   |
| v2         | VIEW       | NULL   |
| v3         | VIEW       | NULL   |
| v56        | VIEW       | NULL   |
+------------+------------+--------+
17 rows in set (0.01 sec)

说明：该语句请求数据库 db5 中所有表的列表，仅显示三条信息：表名、表类型以及存储引擎。Explanation: The statement requests a list of all the tables in database db5, showing just three pieces of information: the name of the table, its type, and its storage engine.

生成的不可见主键的信息默认在所有描述表列、键或两者的 INFORMATION_SCHEMA 表中可见，例如 COLUMNS 和 STATISTICS 表。如果您希望在从这些表中查询时隐藏该信息，可以通过将服务器系统变量 show_gipk_in_create_table_and_information_schema 的值设置为 OFF 来实现。有关更多信息，请参阅 第 15.1.20.11 节，“生成的不可见主键”。Information about generated invisible primary keys is visible by default in all INFORMATION_SCHEMA tables describing table columns, keys, or both, such as the COLUMNS and STATISTICS tables. If you wish to make such information hidden from queries that select from these tables, you can do so by setting the value of the show_gipk_in_create_table_and_information_schema server system variable to OFF. For more information, see Section 15.1.20.11, “Generated Invisible Primary Keys”.

字符列的定义（例如，TABLES.TABLE_NAME）通常为 VARCHAR(N) CHARACTER SET utf8mb3，其中 N 至少为 64。MySQL 对此类列的所有搜索、排序、比较和其他字符串操作使用该字符集的默认校对规则（utf8mb3_general_ci）。The definition for character columns (for example, TABLES.TABLE_NAME) is generally VARCHAR(N) CHARACTER SET utf8mb3 where N is at least 64. MySQL uses the default collation for this character set (utf8mb3_general_ci) for all searches, sorts, comparisons, and other string operations on such columns.

因为某些 MySQL 对象以文件形式表示，所以在 INFORMATION_SCHEMA 字符串列中进行搜索时可能会受到文件系统大小写敏感性的影响。有关更多信息，请参见 第 12.8.7 节，“在 INFORMATION_SCHEMA 搜索中使用排序规则”。Because some MySQL objects are represented as files, searches in INFORMATION_SCHEMA string columns can be affected by file system case sensitivity. For more information, see Section 12.8.7, “Using Collation in INFORMATION_SCHEMA Searches”.

SELECT ... FROM INFORMATION_SCHEMA 语句旨在提供一种更一致的方式来访问 MySQL 支持的各种 SHOW 语句提供的信息（SHOW DATABASES、SHOW TABLES 等等）。与 SHOW 相比，使用 SELECT 具有以下优点：The SELECT ... FROM INFORMATION_SCHEMA statement is intended as a more consistent way to provide access to the information provided by the various SHOW statements that MySQL supports (SHOW DATABASES, SHOW TABLES, and so forth). Using SELECT has these advantages, compared to SHOW:

由于 SHOW 熟悉且被广泛使用，SHOW 语句仍然作为一种替代方案。实际上，随着 INFORMATION_SCHEMA 的实现，SHOW 也有了一些增强，如 第 28.8 节，“SHOW 语句的扩展” 中所述。Because SHOW is familiar and widely used, the SHOW statements remain as an alternative. In fact, along with the implementation of INFORMATION_SCHEMA, there are enhancements to SHOW as described in Section 28.8, “Extensions to SHOW Statements”.

对于大多数 INFORMATION_SCHEMA 表，每个 MySQL 用户都有权访问它们，但只能看到表中与用户拥有适当访问权限的对象对应的行。在某些情况下（例如 INFORMATION_SCHEMA ROUTINES 表中的 ROUTINE_DEFINITION 列），权限不足的用户会看到 NULL。有些表有不同的权限要求；对于这些表，要求会在适用的表描述中提到。例如，InnoDB 表（名称以 INNODB_ 开头的表）需要 PROCESS 权限。For most INFORMATION_SCHEMA tables, each MySQL user has the right to access them, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA ROUTINES table), users who have insufficient privileges see NULL. Some tables have different privilege requirements; for these, the requirements are mentioned in the applicable table descriptions. For example, InnoDB tables (tables with names that begin with INNODB_) require the PROCESS privilege.

选择 INFORMATION_SCHEMA 中的信息以及通过 SHOW 语句查看相同信息时适用相同的权限。在任意一种情况下，你必须对一个对象拥有某种权限才能查看其信息。The same privileges apply to selecting information from INFORMATION_SCHEMA and viewing the same information through SHOW statements. In either case, you must have some privilege on an object to see information about it.

INFORMATION_SCHEMA 查询如果从多个数据库中检索信息，可能会耗费较长时间并影响性能。要检查查询的效率，可以使用 EXPLAIN。有关如何使用 EXPLAIN 输出来优化 INFORMATION_SCHEMA 查询的信息，请参见 第 10.2.3 节，“优化 INFORMATION_SCHEMA 查询”。INFORMATION_SCHEMA queries that search for information from more than one database might take a long time and impact performance. To check the efficiency of a query, you can use EXPLAIN. For information about using EXPLAIN output to tune INFORMATION_SCHEMA queries, see Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”.

MySQL 中 INFORMATION_SCHEMA 表结构的实现遵循 ANSI/ISO SQL:2003 标准第 11 部分 Schemata。我们的目标是大致符合 SQL:2003 核心功能 F021 基本信息架构。The implementation for the INFORMATION_SCHEMA table structures in MySQL follows the ANSI/ISO SQL:2003 standard Part 11 Schemata. Our intent is approximate compliance with SQL:2003 core feature F021 Basic information schema.

使用 SQL Server 2000（也遵循该标准）的用户可能会注意到很强的相似性。然而，MySQL 已经省略了许多对我们的实现不相关的列，并添加了 MySQL 特有的列。其中一个新增的列是 INFORMATION_SCHEMA TABLES 表中的 ENGINE 列。Users of SQL Server 2000 (which also follows the standard) may notice a strong similarity. However, MySQL has omitted many columns that are not relevant for our implementation, and added columns that are MySQL-specific. One such added column is the ENGINE column in the INFORMATION_SCHEMA TABLES table.

虽然其他数据库管理系统使用各种名称，比如 syscat 或 system，但标准名称是 INFORMATION_SCHEMA。Although other DBMSs use a variety of names, like syscat or system, the standard name is INFORMATION_SCHEMA.

为了避免使用在标准或 DB2、SQL Server 或 Oracle 中保留的任何名称，我们更改了一些标记为“MySQL 扩展”的列名。（例如，我们在TABLES 表中将 COLLATION 改为 TABLE_COLLATION。）请参见本文末尾附近的保留字列表：https://web.archive.org/web/20070428032454/http://www.dbazine.com/db2/db2-disarticles/gulutzan5。To avoid using any name that is reserved in the standard or in DB2, SQL Server, or Oracle, we changed the names of some columns marked “MySQL extension”. (For example, we changed COLLATION to TABLE_COLLATION in the TABLES table.) See the list of reserved words near the end of this article: https://web.archive.org/web/20070428032454/http://www.dbazine.com/db2/db2-disarticles/gulutzan5.

以下部分描述了 INFORMATION_SCHEMA 中每个表和列的内容。对于每一列，都有三条信息：The following sections describe each of the tables and columns in INFORMATION_SCHEMA. For each column, there are three pieces of information:

许多部分表明，SHOW 语句相当于从 INFORMATION_SCHEMA 检索信息的 SELECT。对于省略 SHOW 子句而显示默认数据库信息的 FROM db_name 语句，你通常可以通过在从 INFORMATION_SCHEMA 表检索信息的查询的 WHERE 子句中添加 AND TABLE_SCHEMA = SCHEMA() 条件来选择默认数据库的信息。Many sections indicate what SHOW statement is equivalent to a SELECT that retrieves information from INFORMATION_SCHEMA. For SHOW statements that display information for the default database if you omit a FROM db_name clause, you can often select information for the default database by adding an AND TABLE_SCHEMA = SCHEMA() condition to the WHERE clause of a query that retrieves information from an INFORMATION_SCHEMA table.

这些部分讨论了额外的 INFORMATION_SCHEMA 相关主题：These sections discuss additional INFORMATION_SCHEMA-related topics: