Chapter 28 INFORMATION_SCHEMA Tables

Table of Contents

28.1 Introduction

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.

译文：INFORMATION_SCHEMA提供对数据库元数据、MySQL服务器信息（如数据库或表的名称、列的数据类型或访问权限）的访问。有时用于此信息的其他术语是数据字典和系统目录。

INFORMATION_SCHEMA Usage Notes(使用说明)

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.

INFORMATION_SCHEMA是每个MySQL实例中的一个数据库，用于存储MySQL服务器维护的所有其他数据库的信息。INFORMATION_SCHEMA数据库包含几个只读表。它们实际上是视图，而不是基表，因此没有与它们关联的文件，也不能在它们上设置触发器。此外，没有具有该名称的数据库目录。

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.

虽然您可以使用USE语句选择INFORMATION_SCHEMA作为默认数据库，但您只能读取表的内容，而不能对其执行INSERT、UPDATE或DELETE操作。

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)

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.

说明：该语句请求数据库 "db5" 中所有表的列表，仅显示三条信息：表的名称、类型和存储引擎。

Beginning with MySQL 8.0.30, 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”.

从MySQL 8.0.30开始，默认情况下，有关生成的不可见主键的信息在所有描述表列、键或两者的information_SCHEMA表中都是可见的，例如columns和STATISTICS表。如果您希望对从这些表中选择的查询隐藏此类信息，可以通过将show_gipk_in_create_table_and_information_schema服务器系统变量的值设置为OFF来实现。有关更多信息，请参阅第15.1.20.11节“生成的不可见主键”。

Character Set Considerations(注意事项)

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.

字符列（例如TABLES.TABLE_NAME）的定义通常是VARCHAR（N）字符集utf8mb3，其中N至少为64。MySQL对此类列的所有搜索、排序、比较和其他字符串操作使用此字符集的默认排序规则（utf8mb3_general_ci）。

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”.

因为一些MySQL对象表示为文件，所以在INFORMATION_SCHEMA字符串列中的搜索可能会受到文件系统大小写敏感性的影响。有关更多信息，请参阅第12.8.7节“在information_SCHEMA搜索中使用排序规则”。

INFORMATION_SCHEMA as Alternative(替代方案) to SHOW Statements

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:

It conforms to Codd's rules, because all access is done on tables.
You can use the familiar syntax of the SELECT statement, and only need to learn some table and column names.
The implementor need not worry about adding keywords.
You can filter, sort, concatenate, and transform the results from INFORMATION_SCHEMA queries into whatever format your application needs, such as a data structure or a text representation to parse.
This technique is more interoperable with other database systems. For example, Oracle Database users are familiar with querying tables in the Oracle data dictionary.

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”.

"SELECT ... FROM INFORMATION_SCHEMA"语句旨在作为一种更一致的方式，提供对MySQL支持的各种SHOW语句（SHOW数据库、SHOW表等）提供的信息的访问。与SHOW相比，使用SELECT具有以下优点：

它符合Codd的规则，因为所有的访问都是在表上完成的。

您可以使用熟悉的SELECT语句语法，只需要学习一些表名和列名。

实现者不必担心添加关键字。

您可以过滤、排序、连接INFORMATION_SCHEMA查询的结果，并将其转换为应用程序所需的任何格式，例如要解析的数据结构或文本表示。

这种技术与其他数据库系统的互操作性更强。例如，Oracle数据库用户熟悉查询Oracle数据字典中的表。

由于SHOW是熟悉的并且被广泛使用，因此SHOW语句仍然是一种替代方案。事实上，随着INFORMATION_SCHEMA的实现，SHOW也得到了增强，如第28.8节“SHOW语句的扩展”所述。

INFORMATION_SCHEMA and Privileges(权限)

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表，每个MySQL用户都有权访问它们，但只能看到表中与用户具有适当访问权限的对象对应的行。在某些情况下（例如，INFORMATION_SCHEMA ROUTINES表中的ROUTINE_DEFINITION列），权限不足的用户会看到NULL。某些表具有不同的权限要求；对于这些，在适用的表描述中提到了要求。例如，InnoDB表（名称以InnoDB_开头的表）需要PROCESS权限。

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中选择信息，并通过SHOW语句查看相同的信息。在任何一种情况下，您都必须对对象具有某种权限才能查看有关它的信息。

Performance Considerations(性能注意事项)

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”.

从多个数据库中搜索信息的INFORMATION_SCHEMA查询可能需要很长时间，并影响性能。要检查查询的效率，可以使用EXPLAIN。有关使用EXPLAIN输出来优化information_SCHEMA查询的信息，请参阅第10.2.3节“优化information_SCHEMA查询”。

Standards Considerations(标准注意事项)

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.

MySQL中INFORMATION_SCHEMA表结构的实现遵循ANSI/ISO SQL:2003标准第11部分模式。我们的目的是大致符合SQL:2003核心功能F021基本信息模式。

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.

SQL Server 2000（也遵循该标准）的用户可能会注意到很强的相似性。然而，MySQL省略了许多与我们的实现无关的列，并添加了MySQL特定的列。一个这样添加的列是INFORMATION_SCHEMATABLES表中的ENGINE列。

Although other DBMSs use a variety of names, like syscat or system, the standard name is INFORMATION_SCHEMA.

尽管其他DBMS使用各种名称，如syscat或system，但标准名称是INFORMATION_SCHEMA。

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: web.archive.org/web/2007042….

为了避免使用标准中或DB2、SQL Server或Oracle中保留的任何名称，我们更改了一些标记为“MySQL扩展”的列的名称。（例如，我们在TABLES表中将COLLATION更改为TABLE_COLLATION。）请参阅本文末尾附近的保留字列表：web.archive.org/web/2007042…

Conventions in the INFORMATION_SCHEMA Reference Sections(INFORMATION_SCHEMA参考章节中的约定)

The following sections describe each of the tables and columns in INFORMATION_SCHEMA. For each column, there are three pieces of information:

“INFORMATION_SCHEMA Name” indicates the name for the column in the INFORMATION_SCHEMA table. This corresponds to the standard SQL name unless the “Remarks” field says “MySQL extension.”
“SHOW Name” indicates the equivalent field name in the closest SHOW statement, if there is one.
“Remarks” provides additional information where applicable. If this field is NULL, it means that the value of the column is always NULL. If this field says “MySQL extension,” the column is a MySQL extension to standard SQL.

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中的每个表和列。对于每一列，都有三条信息：

“INFO SCHEMA名称”表示INFO SCHEMA表中列的名称。这对应于标准SQL名称，除非“备注”字段显示“MySQL扩展名”

“SHOW Name”表示最接近的SHOW语句中的等效字段名（如果有的话）。

“备注”在适用的情况下提供了额外信息。如果此字段为NULL，则表示列的值始终为NULL。如果此字段显示“MySQL扩展”，则该列是标准SQL的MySQL扩展。

许多部分指出了SHOW语句等效于从information_SCHEMA检索信息的SELECT语句。对于显示默认数据库信息的SHOW语句，如果省略了FROM db_name子句，则通常可以通过向从information_SCHEMA表检索信息的查询的WHERE子句添加AND TABLE_SCHEMA=SCHEMA（）条件来选择默认数据库的信息。

Related Information(相关信息)

These sections discuss additional INFORMATION_SCHEMA-related topics:

information about INFORMATION_SCHEMA tables specific to the InnoDB storage engine: Section 28.4, “INFORMATION_SCHEMA InnoDB Tables”
information about INFORMATION_SCHEMA tables specific to the thread pool plugin: Section 28.5, “INFORMATION_SCHEMA Thread Pool Tables”
information about INFORMATION_SCHEMA tables specific to the CONNECTION_CONTROL plugin: Section 28.6, “INFORMATION_SCHEMA Connection-Control Tables”
Answers to questions that are often asked concerning the INFORMATION_SCHEMA database: Section A.7, “MySQL 8.0 FAQ: INFORMATION_SCHEMA”
INFORMATION_SCHEMA queries and the optimizer: Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”
The effect of collation on INFORMATION_SCHEMA comparisons: Section 12.8.7, “Using Collation in INFORMATION_SCHEMA Searches”

这些部分讨论了其他与信息_SCHEMA相关的主题：

关于特定于InnoDB存储引擎的information_SCHEMA表的信息：第28.4节，“INFORMATER_SCHEMA InnoDB表”

关于特定于线程池插件的information_SCHEMA表的信息：第28.5节，“INFORMATER_SCHEMA线程池表”

关于特定于CONNECTION_CONTROL插件的information_SCHEMA表的信息：第28.6节，“INFORMATER_SCHEMA连接控制表”

关于INFORMATION_SCHEMA数据库的常见问题的答案：第A.7节，“MySQL 8.0常见问题解答：INFORMATER_SCHEMA”

信息_SCHEMA查询和优化器：第10.2.3节，“优化信息_SCHEMA查询”

排序规则对INFORMATION_SCHEMA比较的影响：第12.8.7节，“在INFORMATION_SCHEMA搜索中使用排序规则”

28.2 INFORMATION_SCHEMA Table Reference

The following table summarizes all available INFORMATION_SCHEMA tables. For greater detail, see the individual table descriptions.

下表总结了所有可用的INFORMATION_SCHEMA表。有关更多详细信息，请参阅各个表的说明。

Table 28.1 INFORMATION_SCHEMA Tables

Table Name	Description	Introduced(引进)	Deprecated(废弃)
`ADMINISTRABLE_ROLE_AUTHORIZATIONS`	Grantable users or roles for current user or role	8.0.19
`APPLICABLE_ROLES`	Applicable roles for current user	8.0.19
`CHARACTER_SETS`	Available character sets
`CHECK_CONSTRAINTS`	Table and column CHECK constraints	8.0.16
`COLLATION_CHARACTER_SET_APPLICABILITY`	Character set applicable to each collation
`COLLATIONS`	Collations for each character set
`COLUMN_PRIVILEGES`	Privileges defined on columns
`COLUMN_STATISTICS`	Histogram statistics for column values
`COLUMNS`	Columns in each table
`COLUMNS_EXTENSIONS`	Column attributes for primary and secondary storage engines	8.0.21
`CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS`	Current number of consecutive failed connection attempts per account
`ENABLED_ROLES`	Roles enabled within current session	8.0.19
`ENGINES`	Storage engine properties
`EVENTS`	Event Manager events
`FILES`	Files that store tablespace data
`INNODB_BUFFER_PAGE`	Pages in InnoDB buffer pool
`INNODB_BUFFER_PAGE_LRU`	LRU ordering of pages in InnoDB buffer pool
`INNODB_BUFFER_POOL_STATS`	InnoDB buffer pool statistics
`INNODB_CACHED_INDEXES`	Number of index pages cached per index in InnoDB buffer pool
`INNODB_CMP`	Status for operations related to compressed InnoDB tables
`INNODB_CMP_PER_INDEX`	Status for operations related to compressed InnoDB tables and indexes
`INNODB_CMP_PER_INDEX_RESET`	Status for operations related to compressed InnoDB tables and indexes
`INNODB_CMP_RESET`	Status for operations related to compressed InnoDB tables
`INNODB_CMPMEM`	Status for compressed pages within InnoDB buffer pool
`INNODB_CMPMEM_RESET`	Status for compressed pages within InnoDB buffer pool
`INNODB_COLUMNS`	Columns in each InnoDB table
`INNODB_DATAFILES`	Data file path information for InnoDB file-per-table and general tablespaces
`INNODB_FIELDS`	Key columns of InnoDB indexes
`INNODB_FOREIGN`	InnoDB foreign-key metadata
`INNODB_FOREIGN_COLS`	InnoDB foreign-key column status information
`INNODB_FT_BEING_DELETED`	Snapshot of INNODB_FT_DELETED table
`INNODB_FT_CONFIG`	Metadata for InnoDB table FULLTEXT index and associated processing
`INNODB_FT_DEFAULT_STOPWORD`	Default list of stopwords for InnoDB FULLTEXT indexes
`INNODB_FT_DELETED`	Rows deleted from InnoDB table FULLTEXT index
`INNODB_FT_INDEX_CACHE`	Token information for newly inserted rows in InnoDB FULLTEXT index
`INNODB_FT_INDEX_TABLE`	Inverted index information for processing text searches against InnoDB table FULLTEXT index
`INNODB_INDEXES`	InnoDB index metadata
`INNODB_METRICS`	InnoDB performance information
`INNODB_SESSION_TEMP_TABLESPACES`	Session temporary-tablespace metadata	8.0.13
`INNODB_TABLES`	InnoDB table metadata
`INNODB_TABLESPACES`	InnoDB file-per-table, general, and undo tablespace metadata
`INNODB_TABLESPACES_BRIEF`	Brief file-per-table, general, undo, and system tablespace metadata
`INNODB_TABLESTATS`	InnoDB table low-level status information
`INNODB_TEMP_TABLE_INFO`	Information about active user-created InnoDB temporary tables
`INNODB_TRX`	Active InnoDB transaction information
`INNODB_VIRTUAL`	InnoDB virtual generated column metadata
`KEY_COLUMN_USAGE`	Which key columns have constraints
`KEYWORDS`	MySQL keywords
`MYSQL_FIREWALL_USERS`	Firewall in-memory data for account profiles		8.0.26
`MYSQL_FIREWALL_WHITELIST`	Firewall in-memory data for account profile allowlists		8.0.26
`ndb_transid_mysql_connection_map`	NDB transaction information
`OPTIMIZER_TRACE`	Information produced by optimizer trace activity
`PARAMETERS`	Stored routine parameters and stored function return values
`PARTITIONS`	Table partition information
`PLUGINS`	Plugin information
`PROCESSLIST`	Information about currently executing threads
`PROFILING`	Statement profiling information
`REFERENTIAL_CONSTRAINTS`	Foreign key information
`RESOURCE_GROUPS`	Resource group information
`ROLE_COLUMN_GRANTS`	Column privileges for roles available to or granted by currently enabled roles	8.0.19
`ROLE_ROUTINE_GRANTS`	Routine privileges for roles available to or granted by currently enabled roles	8.0.19
`ROLE_TABLE_GRANTS`	Table privileges for roles available to or granted by currently enabled roles	8.0.19
`ROUTINES`	Stored routine information
`SCHEMA_PRIVILEGES`	Privileges defined on schemas
`SCHEMATA`	Schema information
`SCHEMATA_EXTENSIONS`	Schema options	8.0.22
`ST_GEOMETRY_COLUMNS`	Columns in each table that store spatial data
`ST_SPATIAL_REFERENCE_SYSTEMS`	Available spatial reference systems
`ST_UNITS_OF_MEASURE`	Acceptable units for ST_Distance()	8.0.14
`STATISTICS`	Table index statistics
`TABLE_CONSTRAINTS`	Which tables have constraints
`TABLE_CONSTRAINTS_EXTENSIONS`	Table constraint attributes for primary and secondary storage engines	8.0.21
`TABLE_PRIVILEGES`	Privileges defined on tables
`TABLES`	Table information
`TABLES_EXTENSIONS`	Table attributes for primary and secondary storage engines	8.0.21
`TABLESPACES`	Tablespace information		8.0.22
`TABLESPACES_EXTENSIONS`	Tablespace attributes for primary storage engines	8.0.21
`TP_THREAD_GROUP_STATE`	Thread pool thread group states
`TP_THREAD_GROUP_STATS`	Thread pool thread group statistics
`TP_THREAD_STATE`	Thread pool thread information
`TRIGGERS`	Trigger information
`USER_ATTRIBUTES`	User comments and attributes	8.0.21
`USER_PRIVILEGES`	Privileges defined globally per user
`VIEW_ROUTINE_USAGE`	Stored functions used in views	8.0.13
`VIEW_TABLE_USAGE`	Tables and views used in views	8.0.13
`VIEWS`	View information

28.3 INFORMATION_SCHEMA General Tables

The following sections describe what may be denoted as the “general” set of INFORMATION_SCHEMA tables. These are the tables not associated with particular storage engines, components, or plugins.

以下部分描述了可以表示为“通用”的INFORMATION_SCHEMA表集。这些表与特定的存储引擎、组件或插件无关。

28.3.1 ~ 28.3.37 内容暂无

28.3.38 The INFORMATION_SCHEMA TABLES Table

The TABLES table provides information about tables in databases.

TABLES表提供有关数据库中表的信息。

Columns in TABLES that represent table statistics hold cached values. The information_schema_stats_expiry system variable defines the period of time before cached table statistics expire. The default is 86400 seconds (24 hours). If there are no cached statistics or statistics have expired, statistics are retrieved from storage engines when querying table statistics columns. To update cached values at any time for a given table, use ANALYZE TABLE. To always retrieve the latest statistics directly from storage engines, set information_schema_stats_expiry to 0. For more information, see Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”.

Note

If the innodb_read_only system variable is enabled, ANALYZE TABLE may fail because it cannot update statistics tables in the data dictionary, which use InnoDB. For ANALYZE TABLE operations that update the key distribution, failure may occur even if the operation updates the table itself (for example, if it is a MyISAM table). To obtain the updated distribution statistics, set information_schema_stats_expiry=0.

如果启用了innodb_read_only系统变量，ANALYZE TABLE可能会失败，因为它无法更新使用innodb的数据字典中的统计表。对于更新密钥分布的ANALYZE TABLE操作，即使该操作更新了表本身（例如，如果它是MyISAM表），也可能会发生故障。要获取更新的分发统计信息，请将information_schema_stats_expiry=0设置为。

TABLES中表示表统计信息的列保存缓存值。information_schema_stats_expiry系统变量定义了缓存表统计信息过期之前的时间段。默认值为86400秒（24小时）。如果没有缓存的统计信息或统计信息已过期，则在查询表统计信息列时将从存储引擎检索统计信息。要随时更新给定表的缓存值，请使用ANALYZE table。要始终直接从存储引擎检索最新统计信息，请将information_schema_stats_expiry设置为0。有关更多信息，请参阅第10.2.3节“优化information_SCHEMA查询”。

The TABLES table has these columns:

TABLE_CATALOG

The name of the catalog to which the table belongs. This value is always def.
TABLE_SCHEMA

The name of the schema (database) to which the table belongs.
TABLE_NAME

The name of the table.
TABLE_TYPE

BASE TABLE for a table, VIEW for a view, or SYSTEM VIEW for an INFORMATION_SCHEMA table.

The TABLES table does not list TEMPORARY tables.
ENGINE

The storage engine for the table. See Chapter 17, The InnoDB Storage Engine, and Chapter 18, Alternative Storage Engines.

For partitioned tables, ENGINE shows the name of the storage engine used by all partitions.

对于分区表，ENGINE显示所有分区使用的存储引擎的名称。
VERSION

This column is unused. With the removal of .frm files in MySQL 8.0, this column now reports a hardcoded value of 10, which is the last .frm file version used in MySQL 5.7.

此列未使用。随着MySQL 8.0中.frm文件的删除，本专栏现在报告了一个硬编码值10，这是MySQL 5.7中使用的最后一个.frm文件版本。
ROW_FORMAT

The row-storage format (Fixed, Dynamic, Compressed, Redundant, Compact). For MyISAM tables, Dynamic corresponds to what myisamchk -dvv reports as Packed.

行存储格式（固定、动态、压缩、冗余、紧凑）。对于MyISAM表，Dynamic对应于myisamchk-dvv报告为打包的内容。
TABLE_ROWS

The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

行数。一些存储引擎，如MyISAM，存储确切的计数。对于其他存储引擎，如InnoDB，此值是近似值，可能与实际值相差40%至50%。在这种情况下，使用SELECT COUNT（*）获得准确的计数。

TABLE_ROWS is NULL for INFORMATION_SCHEMA tables.

对于INFORMATION_SCHEMA表，TABLE_ROWS为NULL。

For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

对于InnoDB表，行数只是SQL优化中使用的粗略估计。（如果InnoDB表被分区，则也是如此。）
AVG_ROW_LENGTH

The average row length.
DATA_LENGTH

For MyISAM, DATA_LENGTH is the length of the data file, in bytes.

For InnoDB, DATA_LENGTH is the approximate amount of space allocated for the clustered index, in bytes. Specifically, it is the clustered index size, in pages, multiplied by the InnoDB page size.

Refer to the notes at the end of this section for information regarding other storage engines.

对于MyISAM，DATA_LENGTH是数据文件的长度，单位为字节。

对于InnoDB，DATA_LENGTH是为聚集索引分配的大致空间量，单位为字节。具体来说，它是聚集索引大小（以页为单位）乘以InnoDB页大小。

有关其他存储引擎的信息，请参阅本节末尾的注释。
MAX_DATA_LENGTH

For MyISAM, MAX_DATA_LENGTH is maximum length of the data file. This is the total number of bytes of data that can be stored in the table, given the data pointer size used.

Unused for InnoDB.

Refer to the notes at the end of this section for information regarding other storage engines.

对于MyISAM，MAX_DATA_LENGTH是数据文件的最大长度。这是给定使用的数据指针大小，可以存储在表中的数据字节总数。

未用于InnoDB。

有关其他存储引擎的信息，请参阅本节末尾的注释。
INDEX_LENGTH

For MyISAM, INDEX_LENGTH is the length of the index file, in bytes.

For InnoDB, INDEX_LENGTH is the approximate amount of space allocated for non-clustered indexes, in bytes. Specifically, it is the sum of non-clustered index sizes, in pages, multiplied by the InnoDB page size.

Refer to the notes at the end of this section for information regarding other storage engines.

对于MyISAM，INDEX_LENGTH是索引文件的长度，单位为字节。

对于InnoDB，INDEX_LENGTH是为非聚集索引分配的大致空间量，单位为字节。具体来说，它是以页为单位的非聚集索引大小的总和乘以InnoDB页大小。
DATA_FREE

The number of allocated but unused bytes.

已分配但未使用的字节数。

InnoDB tables report the free space of the tablespace to which the table belongs. For a table located in the shared tablespace, this is the free space of the shared tablespace. If you are using multiple tablespaces and the table has its own tablespace, the free space is for only that table. Free space means the number of bytes in completely free extents minus a safety margin. Even if free space displays as 0, it may be possible to insert rows as long as new extents need not be allocated.

InnoDB表报告表所属表空间的可用空间。对于位于共享表空间中的表，这是共享表空间的可用空间。如果您使用多个表空间，并且表有自己的表空间，则可用空间仅用于该表。可用空间是指完全可用范围中的字节数减去安全裕度。即使可用空间显示为0，只要不需要分配新的扩展数据块，就可以插入行。

For NDB Cluster, DATA_FREE shows the space allocated on disk for, but not used by, a Disk Data table or fragment on disk. (In-memory data resource usage is reported by the DATA_LENGTH column.)

对于NDB群集，DATA_FREE显示磁盘上为磁盘上的磁盘数据表或片段分配但未被其使用的空间。（内存中的数据资源使用情况由data_LENGTH列报告。）

For partitioned tables, this value is only an estimate and may not be absolutely correct. A more accurate method of obtaining this information in such cases is to query the INFORMATION_SCHEMA PARTITIONS table, as shown in this example:

对于分区表，此值只是估计值，可能不是绝对正确的。在这种情况下，获取此信息的一种更准确的方法是查询information_SCHEMA PARTITIONS 表，如下例所示：
```
SELECT SUM(DATA_FREE)
    FROM  INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_SCHEMA = 'mydb'
    AND   TABLE_NAME   = 'mytable';
```
For more information, see Section 28.3.21, “The INFORMATION_SCHEMA PARTITIONS Table”.
AUTO_INCREMENT

The next AUTO_INCREMENT value.

下一个AUTO_INCREMENT值。
CREATE_TIME

When the table was created.
UPDATE_TIME

When the table was last updated. For some storage engines, this value is NULL. Even with file-per-table mode with each InnoDB table in a separate .ibd file, change buffering can delay the write to the data file, so the file modification time is different from the time of the last insert, update, or delete. For MyISAM, the data file timestamp is used; however, on Windows the timestamp is not updated by updates, so the value is inaccurate.

上次更新表格的时间。对于某些存储引擎，此值为NULL。即使在每个InnoDB表都在一个单独的.ibd文件中的每表文件模式下，更改缓冲也会延迟对数据文件的写入，因此文件修改时间与上次插入、更新或删除的时间不同。对于MyISAM，使用数据文件时间戳；但是，在Windows上，时间戳不会随更新而更新，因此该值不准确。

UPDATE_TIME displays a timestamp value for the last UPDATE, INSERT, or DELETE performed on InnoDB tables that are not partitioned. For MVCC, the timestamp value reflects the COMMIT time, which is considered the last update time. Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache.

UPDATE_TIME显示对未分区的InnoDB表执行的最后一次UPDATE、INSERT或DELETE的时间戳值。对于MVCC，时间戳值反映了COMMIT时间，即最后一次更新时间。当服务器重新启动或表从InnoDB数据字典缓存中移出时，时间戳不会持久化。
CHECK_TIME

When the table was last checked. Not all storage engines update this time, in which case, the value is always NULL.

上次检查 table 是什么时候。并非所有存储引擎这次都会更新，在这种情况下，该值始终为NULL。

For partitioned InnoDB tables, CHECK_TIME is always NULL.
TABLE_COLLATION

The table default collation. The output does not explicitly list the table default character set, but the collation name begins with the character set name.

表默认排序规则。输出没有明确列出表默认字符集，但排序规则名称以字符集名称开头。
CHECKSUM

The live checksum value, if any.

实时校验和值（如果有的话）。
CREATE_OPTIONS

Extra options used with CREATE TABLE.

CREATE TABLE使用的额外选项。

CREATE_OPTIONS shows partitioned for a partitioned table.

CREATE_OPTIONS显示分区表的分区。

Prior to MySQL 8.0.16, CREATE_OPTIONS shows the ENCRYPTION clause specified for tables created in file-per-table tablespaces. As of MySQL 8.0.16, it shows the encryption clause for file-per-table tablespaces if the table is encrypted or if the specified encryption differs from the schema encryption. The encryption clause is not shown for tables created in general tablespaces. To identify encrypted file-per-table and general tablespaces, query the INNODB_TABLESPACES ENCRYPTION column.

在MySQL 8.0.16之前，CREATE_OPTIONS显示了为在file-per-table表空间中创建的表指定的ENCRYPTION子句。从MySQL 8.0.16开始，如果表被加密或指定的加密与模式加密不同，它将显示每个表表空间的文件加密子句。对于在常规表空间中创建的表，未显示加密子句。要识别每个表和常规表空间的加密文件，请查询INNODB_TABLEACES ENCRYPTION列。

When creating a table with strict mode disabled, the storage engine's default row format is used if the specified row format is not supported. The actual row format of the table is reported in the ROW_FORMAT column. CREATE_OPTIONS shows the row format that was specified in the CREATE TABLE statement.

在创建禁用严格模式的表时，如果不支持指定的行格式，则使用存储引擎的默认行格式。表的实际行格式在row_format列中报告。CREATE_OPTIONS显示在CREATE TABLE语句中指定的行格式。

When altering the storage engine of a table, table options that are not applicable to the new storage engine are retained in the table definition to enable reverting the table with its previously defined options to the original storage engine, if necessary. The CREATE_OPTIONS column may show retained options.

更改表的存储引擎时，不适用于新存储引擎的表选项将保留在表定义中，以便在必要时将表及其先前定义的选项还原为原始存储引擎。CREATE_OPTIONS列可能显示保留的选项。
TABLE_COMMENT

The comment used when creating the table (or information as to why MySQL could not access the table information).

创建表时使用的注释（或MySQL无法访问表信息的原因）。

Notes

For NDB tables, the output of this statement shows appropriate values for the AVG_ROW_LENGTH and DATA_LENGTH columns, with the exception that BLOB columns are not taken into account.
For NDB tables, DATA_LENGTH includes data stored in main memory only; the MAX_DATA_LENGTH and DATA_FREE columns apply to Disk Data.
For NDB Cluster Disk Data tables, MAX_DATA_LENGTH shows the space allocated for the disk part of a Disk Data table or fragment. (In-memory data resource usage is reported by the DATA_LENGTH column.)
For MEMORY tables, the DATA_LENGTH, MAX_DATA_LENGTH, and INDEX_LENGTH values approximate the actual amount of allocated memory. The allocation algorithm reserves memory in large amounts to reduce the number of allocation operations.
For views, most TABLES columns are 0 or NULL except that TABLE_NAME indicates the view name, CREATE_TIME indicates the creation time, and TABLE_COMMENT says VIEW.

对于NDB表，此语句的输出显示了AVG_ROW_LENGTH和DATA_LENGTH列的适当值，但不考虑BLOB列。

对于NDB表，DATA_LENGTH仅包括存储在主存储器中的数据；MAX_DATA_LENGTH和DATA_FREE列适用于磁盘数据。

对于NDB群集磁盘数据表，MAX_Data_LENGTH显示为磁盘数据表或片段的磁盘部分分配的空间。（内存中的数据资源使用情况由data_LENGTH列报告。）

对于内存表，DATA_LENGTH、MAX_DATA_LENGTH和INDEX_LENGTH值近似于实际分配的内存量。分配算法保留大量内存以减少分配操作的数量。

对于视图，大多数TABLES列都是0或NULL，除了TABLE_NAME表示视图名称，CREATE_TIME表示创建时间，TABLE_COMMENT表示view。

Table information is also available from the SHOW TABLE STATUS and SHOW TABLES statements. See Section 15.7.7.38, “SHOW TABLE STATUS Statement”, and Section 15.7.7.39, “SHOW TABLES Statement”. The following statements are equivalent:

表信息也可从SHOW Table STATUS和SHOW TABLES语句中获得。见第15.7.7.38节“显示表格状态声明”和第15.7.7.3.9节“显示图表声明”。以下陈述是等效的：

SELECT
    TABLE_NAME, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH,
    DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT,
    CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM,
    CREATE_OPTIONS, TABLE_COMMENT
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SHOW TABLE STATUS
  FROM db_name
  [LIKE 'wild']

The following statements are equivalent:(以下陈述是等效的：)

SELECT
  TABLE_NAME, TABLE_TYPE
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SHOW FULL TABLES
  FROM db_name
  [LIKE 'wild']

MySQL Chapter 28