MySQL :: MySQL 8.4 参考手册 :: 28.3.38 INFORMATION

8.4 中文镜像

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

打开官方英文原文 ↗

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 28 个 INFORMATION_SCHEMA 表 / 28.3 INFORMATION_SCHEMA 总表 / 28.3.38 INFORMATION_SCHEMA TABLES 表

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 77%

28.3.38 INFORMATION_SCHEMA TABLES 表28.3.38 The INFORMATION_SCHEMA TABLES Table

TABLES 表提供数据库中表的信息。The TABLES table provides information about tables in databases.

在 TABLES 中表示表统计信息的列保存的是缓存值。information_schema_stats_expiry 系统变量定义了缓存表统计信息过期前的时间周期。默认值是 86400 秒（24 小时）。如果没有缓存的统计信息或统计信息已过期，则在查询表统计信息列时会从存储引擎获取统计信息。要随时更新给定表的缓存值，请使用 ANALYZE TABLE。要始终直接从存储引擎获取最新统计信息，请将 information_schema_stats_expiry 设置为 0。更多信息，请参见第 10.2.3 节，“优化 INFORMATION_SCHEMA 查询”。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

如果启用了 innodb_read_only 系统变量，ANALYZE TABLE 可能会失败，因为它无法更新使用 InnoDB 的数据字典中的统计表。对于更新键分布的 ANALYZE TABLE 操作，即使操作更新了表本身（例如，如果它是 MyISAM 表），也可能会失败。要获得更新的分布统计信息，请设置 information_schema_stats_expiry=0。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.

TABLES 表具有以下列：The TABLES table has these columns:

TABLE_CATALOG
表所属的目录名称。该值始终为 def。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_NAMETABLE_NAME
表的名称。The name of the table.
TABLE_TYPE
BASE TABLE 表示一个表，VIEW 表示一个视图，或 SYSTEM VIEW 表示一个 INFORMATION_SCHEMA 表。BASE TABLE for a table, VIEW for a view, or SYSTEM VIEW for an INFORMATION_SCHEMA table.
TABLES 表不列出 TEMPORARY 表。The TABLES table does not list TEMPORARY tables.
ENGINEENGINE
表的存储引擎。请参见第 17 章，InnoDB 存储引擎，以及第 18 章，其他存储引擎。The storage engine for the table. See Chapter 17, The InnoDB Storage Engine, and Chapter 18, Alternative Storage Engines.
对于分区表，ENGINE 显示所有分区使用的存储引擎名称。For partitioned tables, ENGINE shows the name of the storage engine used by all partitions.
VERSION
此列未使用。在 MySQL 8.0 中移除 .frm 文件后，此列现在报告一个硬编码值 10，这是 MySQL 5.7 中使用的最后一个 .frm 文件版本。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.
ROW_FORMATROW_FORMAT
行存储格式（Fixed、Dynamic、Compressed、Redundant、Compact）。对于 MyISAM 表，Dynamic 对应于 myisamchk -dvv 报告的 Packed。The row-storage format (Fixed, Dynamic, Compressed, Redundant, Compact). For MyISAM tables, Dynamic corresponds to what myisamchk -dvv reports as Packed.
TABLE_ROWS
行数。一些存储引擎，例如 MyISAM，存储确切的行数。对于其他存储引擎，例如 InnoDB，此值是一个近似值，可能与实际值相差达到 40% 到 50%。在这种情况下，请使用 SELECT COUNT(*) 来获得准确的行数。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.
TABLE_ROWS 对于 INFORMATION_SCHEMA 表是 NULL。TABLE_ROWS is NULL for INFORMATION_SCHEMA tables.
对于 InnoDB 表，行数只是用于 SQL 优化的大致估计值。（如果 InnoDB 表被分区，这也同样适用。）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.)
AVG_ROW_LENGTH
平均行长度。The average row length.
DATA_LENGTH
对于 MyISAM，DATA_LENGTH 是数据文件的长度，以字节为单位。For MyISAM, DATA_LENGTH is the length of the data file, in bytes.
对于 InnoDB，DATA_LENGTH 是为聚簇索引分配的大致空间量，单位为字节。具体来说，它是聚簇索引的大小（以页为单位）乘以 InnoDB 页大小。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.
MAX_DATA_LENGTH
对于 MyISAM，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.
在 InnoDB 中未使用。Unused for InnoDB.
有关其他存储引擎的信息，请参阅本节末尾的注释。Refer to the notes at the end of this section for information regarding other storage engines.
INDEX_LENGTH
对于 MyISAM，INDEX_LENGTH 是索引文件的长度，单位为字节。For MyISAM, INDEX_LENGTH is the length of the index file, in bytes.
对于 InnoDB，INDEX_LENGTH 是为非聚簇索引分配的大致空间量，单位为字节。具体来说，它是非聚簇索引大小（以页为单位）的总和，乘以 InnoDB 页大小。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.
DATA_FREE
已分配但未使用的字节数。The number of allocated but unused bytes.
InnoDB 表报告其所属表空间的空闲空间。对于位于共享表空间的表，这是共享表空间的空闲空间。如果您使用多个表空间，并且表有自己的表空间，则空闲空间仅针对该表。空闲空间指完全空闲区段中的字节数减去安全边际。即使空闲空间显示为 0，只要不需要分配新的区段，仍然可能插入行。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.
对于 NDB 集群，DATA_FREE 显示在磁盘上为磁盘表或磁盘上的碎片分配但未使用的空间。（内存中的数据资源使用情况由 DATA_LENGTH 列报告。）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.)
对于分区表，此值仅为估算值，可能不完全正确。在这种情况下获取此信息的更准确方法是查询 INFORMATION_SCHEMA PARTITIONS 表，如下示例所示：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:
```
SELECT SUM(DATA_FREE)
    FROM  INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_SCHEMA = 'mydb'
    AND   TABLE_NAME   = 'mytable';
```
有关更多信息，请参阅第 28.3.21 节，“INFORMATION_SCHEMA PARTITIONS 表”。For more information, see Section 28.3.21, “The INFORMATION_SCHEMA PARTITIONS Table”.
AUTO_INCREMENT
下一个 AUTO_INCREMENT 值。The next AUTO_INCREMENT value.
CREATE_TIME
表创建的时间。When the table was created.
UPDATE_TIME
表最后更新的时间。对于某些存储引擎，此值为NULL。即使在每表文件模式下，每个InnoDB表在单独的.ibd文件中，更改缓冲也可能会延迟写入数据文件，因此文件修改时间与最后一次插入、更新或删除的时间不同。对于MyISAM，使用数据文件的时间戳；但是，在 Windows 上，更新不会更新时间戳，所以该值不准确。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.
UPDATE_TIME 显示最后一次在未分区的 InnoDB 表上执行的 UPDATE、INSERT 或 DELETE 的时间戳值。对于 MVCC，该时间戳值反映 COMMIT 时间，此时间被视为最后更新时间。当服务器重启或表从 InnoDB 数据字典缓存中被驳回时，时间戳不会被持久保存。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.
CHECK_TIME
上次检查表的时间。并非所有存储引擎都会更新此时间，在这种情况下，该值始终为 NULL。When the table was last checked. Not all storage engines update this time, in which case, the value is always NULL.
对于分区的 InnoDB 表，CHECK_TIME 始终为 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.
CHECKSUMCHECKSUM
实时校验和的值（如果有）。The live checksum value, if any.
CREATE_OPTIONS
与 CREATE TABLE 一起使用的额外选项。Extra options used with CREATE TABLE.
CREATE_OPTIONS 对分区表显示 partitioned。CREATE_OPTIONS shows partitioned for a partitioned table.
CREATE_OPTIONS 显示在按表文件表空间中创建的表指定的 ENCRYPTION 子句。如果表被加密或指定的加密与模式加密不同，它会显示按表文件表空间的加密子句。对于在通用表空间中创建的表，不显示加密子句。要识别加密的按表文件和通用表空间，请查询 INNODB_TABLESPACES 的 ENCRYPTION 列。CREATE_OPTIONS shows the ENCRYPTION clause specified for tables created in file-per-table tablespaces. 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.
当在禁用严格模式的情况下创建表时，如果指定的行格式不被支持，则使用存储引擎的默认行格式。表的实际行格式显示在ROW_FORMAT列中。CREATE_OPTIONS显示在CREATE TABLE语句中指定的行格式。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.
在更改表的存储引擎时，不适用于新存储引擎的表选项会保留在表定义中，以便在必要时将表及其以前定义的选项恢复到原始存储引擎。CREATE_OPTIONS列可能显示保留的选项。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.
TABLE_COMMENT
创建表时使用的注释（或有关 MySQL 无法访问表信息的原因的信息）。The comment used when creating the table (or information as to why MySQL could not access the table information).

注意事项Notes

对于 NDB 表，该语句的输出显示 AVG_ROW_LENGTH 和 DATA_LENGTH 列的适当值，但 BLOB 列不包括在内。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.
对于 NDB 表，DATA_LENGTH 仅包括存储在主内存中的数据；MAX_DATA_LENGTH 和 DATA_FREE 列适用于磁盘数据。For NDB tables, DATA_LENGTH includes data stored in main memory only; the MAX_DATA_LENGTH and DATA_FREE columns apply to Disk Data.
对于 NDB 集群磁盘表，MAX_DATA_LENGTH 显示为磁盘表或分片的磁盘部分分配的空间。（内存数据资源使用情况由 DATA_LENGTH 列报告。）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.)
对于 MEMORY 表，DATA_LENGTH、MAX_DATA_LENGTH 和 INDEX_LENGTH 的值大致表示实际分配的内存量。分配算法以大块方式保留内存，以减少分配操作的次数。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.
对于视图，大多数 TABLES 列都是 0 或 NULL，除了 TABLE_NAME 表示视图名称，CREATE_TIME 表示创建时间，以及 TABLE_COMMENT 显示 VIEW。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.

表信息还可以通过 SHOW TABLE STATUS 和 SHOW TABLES 语句获取。参见第 15.7.7.38 节，“SHOW TABLE STATUS 语句”，以及第 15.7.7.39 节，“SHOW TABLES 语句”。以下语句是等效的：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:

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']

首页上一级下一节