The INNODB_SYS_TABLESPACES
table
provides metadata about InnoDB
tablespaces,
equivalent to the information in the
SYS_TABLESPACES
table in the
InnoDB
data dictionary.
For related usage information and examples, see Section 14.15.3, “InnoDB INFORMATION_SCHEMA System Tables”.
The INNODB_SYS_TABLESPACES
table has
these columns:
SPACE
The tablespace ID.
NAME
The schema (database) and table name.
FLAG
A numeric value that represents bit-level information about tablespace format and storage characteristics.
FILE_FORMAT
The tablespace file format (for example, Antelope or Barracuda). The data in this field is interpreted from the tablespace flags information that resides in the .ibd file. For more information about
InnoDB
file formats, see Section 14.10, “InnoDB File-Format Management”.ROW_FORMAT
The tablespace row format (
Compact or Redundant
,Dynamic
, orCompressed
). The data in this column is interpreted from the tablespace flags information that resides in the.ibd
file.PAGE_SIZE
The tablespace page size. The data in this column is interpreted from the tablespace flags information that resides in the
.ibd
file.ZIP_PAGE_SIZE
The tablespace zip page size. The data in this column is interpreted from the tablespace flags information that resides in the
.ibd
file.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE = 57\G
*************************** 1. row ***************************
SPACE: 57
NAME: test/t1
FLAG: 0
FILE_FORMAT: Antelope
ROW_FORMAT: Compact or Redundant
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
Notes
You must have the
PROCESS
privilege to query this table.Use the
INFORMATION_SCHEMA
COLUMNS
table or theSHOW COLUMNS
statement to view additional information about the columns of this table, including data types and default values.Because tablespace flags are always zero for all Antelope file formats (unlike table flags), there is no way to determine from this flag integer if the tablespace row format is Redundant or Compact. As a result, the possible values for the
ROW_FORMAT
field are “Compact or Redundant”, “Compressed”, or “Dynamic.”