26.38 INFORMATION_SCHEMA TABLES 表

官方文档地址：26.38 The INFORMATION_SCHEMA TABLES Table

---

TABLES表用来提供数据库中的表信息。

TABLES表中记录统计信息的列保存的是缓存值，可以通过系统变量information_schema_stats_expiry定义这些缓存值的过期时间。默认值是86400秒（24小时）。如果统计信息没有缓存或者统计信息已过期，则在查询表统计列时会从存储引擎检索统计信息。如果要查询随时更新给定表的缓存值，请使用ANALYZE TABLE。如果想要直接从存储引擎检索最新统计信息，请将information_schema_stats_expiry设置为0。有关更多信息，请参见 8.2.3 优化 INFORMATION_SCHEMA 查询。

注意

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

TABLES表有以下列：

• TABLE_CATALOG：表所属的目录的名称。此值始终为def。
• TABLE_SCHEMA：表所属的模式（数据库）的名称。
• TABLE_NAME：表的名称。
• TABLE_TYPE：BASE TABLE表示一个表，VIEW表示一个视图，SYSTEM VIEW表示一个INFORMATION_SCHEMA表。TABLES表没有列出TEMPORARY表。
• ENGINE：表的存储引擎。参见 第15章 InnoDB 存储引擎 和 第16章 可供选择的存储引擎。对于分区表，ENGINE显示所有分区使用的存储引擎的名称。
• VERSION：此列未使用。由于 MySQL 8.0 中删除了.frm文件，这一列现在记录一个硬编码值为10，这是 MySQL 5.7 中使用的最后一个.frm文件版本。
• ROW_FORMAT：可存储的值：Fixed，Dynamic，Compressed，Redundant，Compact。对于MyISAM表，Dynamic对应的是myisamchk -dvv记录的Packed。
• TABLE_ROWS：行数。一些存储引擎，如MyISAM，存储精确的计数。对于其他存储引擎，例如InnoDB，这个值只是一个近似值，可能与实际值相差40%到50%。在这种情况下，使用SELECT COUNT(*)来获得准确的计数。对于INFORMATION_SCHEMA表，TABLE_ROWS为NULL。对于InnoDB表，行数只是SQL优化中使用的粗略估计（InnoDB分区表一样）。
• AVG_ROW_LENGTH：行的平均长度。
• DATA_LENGTH：对于MyISAM，DATA_LENGTH是数据文件的长度，以字节为单位。对于InnoDB，DATA_LENGTH是为聚集索引分配的大约空间量，以字节为单位。具体来说，它是聚集索引大小（以页为单位）乘以InnoDB页数。有关其他存储引擎的信息，请参见本节末尾的说明。
• MAX_DATA_LENGTH：对于MyISAM，MAX_DATA_LENGTH是数据文件的最大长度。这是给定所使用的数据指针大小，可以存储在表中的数据字节总数。InnoDB未使用，值为0。有关其他存储引擎的信息，请参见本节末尾的说明。
• INDEX_LENGTH：对于MyISAM，INDEX_LENGTH是索引文件的长度，以字节为单位。对于InnoDB，INDEX_LENGTH是分配给非聚集索引的大约空间量，以字节为单位。具体来说，它是非聚集索引大小（以页为单位）乘以InnoDB页数。有关其他存储引擎的信息，请参见本节末尾的说明。
• DATA_FREE：已分配但未使用的字节数。InnoDB表会报告该表所属表空间的空闲空间。对于位于共享表空间中的表，这是共享表空间的空闲空间。如果使用多个表空间，并且表有自己的表空间，则空闲空间仅用于该表。空闲空间是指完全空闲区段减去安全裕度的字节数。即使空闲空间显示为0，只要不需要分配新的区段，就可以插入行。
  对于 NDB Cluster，DATA_FREE显示磁盘上为磁盘数据表或磁盘片段分配但未被使用的空间。（内存中的数据资源使用情况由DATA_LENGTH列报告。）
  对于分区表，这个值只是一个估计值，可能不是绝对正确的。在这种情况下，更准确的获取此信息的方法是查询INFORMATION_SCHEMA PARTITIONS表，如本例所示：
  SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'mytable';
  有关更多信息，请参见 26.21 INFORMATION_SCHEMA PARTITIONS 表。
• AUTO_INCREMENT：下一个AUTO_INCREMENT值。
• CREATE_TIME：创建表的时间。
• UPDATE_TIME：上次更新数据文件的时间。对于某些存储引擎，该值为NULL。例如，InnoDB在其system tablespace中存储多个表，而数据文件没有应用时间戳。即使在file-per-table模式下，每个InnoDB表都在一个单独的.ibd文件中，更改缓冲区也会延迟对数据文件的写操作，因此文件修改时间与最后一次插入、更新或删除的时间不同。对于MyISAM，使用数据文件的时间戳；但是，在 Windows 上，时间戳不会通过更新来更新，所以值是不准确的。
  UPDATE_TIME显示在未分区的InnoDB表上执行的最后一次更新、插入或删除的时间戳值。对于 MVCC，时间戳值反映提交时间，它被认为是最后一次更新时间。当服务器重新启动或者从InnoDB数据字典缓存中删除表时，时间戳不会持久。
• CHECK_TIME：当最后一次检查表时。不是所有的存储引擎都更新这个时间，在这种情况下，该值总是NULL。对于分区InnoDB表，CHECK_TIME总是为NULL。
• TABLE_COLLATION：表的默认排序规则。输出不会显式列出表的默认字符集，但排序规则名称以字符集名称开头。
• CHECKSUM：如果有的话，实时校验和值。
• CREATE_OPTIONS：创建表时使用的额外选项。CREATE_OPTIONS显示分区表的分区情况。在 MySQL 8.0.16 之前，CREATE_OPTIONS显示了为在file-per-table表空间中创建的表指定的ENCRYPTION子句。从 MySQL 8.0.16 开始，如果表被加密，或者指定的加密不同于模式加密，它将显示针对每个表文件的表空间的加密子句。对于在一般表空间中创建的表，不会显示加密子句。要识别加密的每个表文件和普通表空间，查询INNODB_TABLESPACES表中的ENCRYPTION列。
  
  当创建严格模式禁用的表时，如果指定的行格式不支持，则使用存储引擎的默认行格式。表的实际行格式在ROW_FORMAT列中报告。CREATE_OPTIONS显示在CREATE TABLE语句中指定的行格式。
  
  在更改表的存储引擎时，表定义中保留不适用于新存储引擎的表选项，以便在必要时将表及其先前定义的选项恢复到原始存储引擎。CREATE_OPTIONS列可以显示保留的选项。
• TABLE_COMMENT：创建表时使用的注释（或者关于为什么MySQL不能访问表信息的信息）。

注意事项：

• 对于 NDB 表，该表单的输出显示了AVG_ROW_LENGTH和DATA_LENGTH列的适当值，但没有考虑BLOB列。
• 对于 NDB 表，DATA_LENGTH只包含主存中的数据；MAX_DATA_LENGTH和DATA_FREE列适用于硬盘数据。
• 对于 NDB Cluster 磁盘数据表，MAX_DATA_LENGTH显示为磁盘数据表或片段的磁盘部分分配的空间。（内存中的数据资源使用情况由DATA_LENGTH列报告。）
• 对于 MEMORY 表，DATA_LENGTH、MAX_DATA_LENGTH和INDEX_LENGTH值近似于实际分配的内存量。分配算法大量预留内存，减少分配操作次数。
• 对于视图，除了TABLE_NAME表示视图名、CREATE_TIME表示创建时间、TABLE_COMMENT值为VIEW之外，大多数表的列都是0或NULL。

表信息也可以从SHOW TABLE STATUS和SHOW TABLES语句中获得。参见 13.7.7.38 SHOW TABLE STATUS 语句 以及 13.7.7.39 SHOW TABLES 语句。下面的语句是等价的：

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

SHOW TABLE STATUS FROM db_name [LIKE 'tb_name']

下面的语句是等价的：

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

SHOW FULL TABLES FROM db_name [LIKE 'tb_name']