官方文档地址: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']