21.29.7 The INFORMATION_SCHEMA INNODB_SYS_TABLES Table

The INNODB_SYS_TABLES table provides metadata about InnoDB tables, equivalent to the information from the SYS_TABLES table in the InnoDB data dictionary.

Table 21.7 INNODB_SYS_TABLES Columns

Column nameDescription
TABLE_IDAn identifier for each InnoDB table that is unique across all databases in the instance.
NAMEThe name of the table. Preceded by the database name where appropriate, for example test/t1. InnoDB system table names are in all uppercase. Names of databases and user tables are in the same case as they were originally defined, possibly influenced by the lower_case_table_names setting.
FLAGThis value provides bit level information about table format and storage characteristics including row format, compressed page size (if applicable), and whether or not the DATA DIRECTORY clause was used with CREATE TABLE or ALTER TABLE.
N_COLSThe number of columns in the table. The number reported includes three hidden columns that are created by InnoDB (DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR).
SPACEAn identifier for the tablespace where the table resides. 0 means the InnoDB system tablespace. Any other number represents a table created in file-per-table mode with a separate .ibd file. This identifier stays the same after a TRUNCATE TABLE statement. Other than the zero value, this identifier is unique for tables across all the databases in the instance.
FILE_FORMATThe table's file format (Antelope or Barracuda).
ROW_FORMATThe table's row format (Compact, Redundant, Dynamic, or Compressed).
ZIP_PAGE_SIZEThe zip page size. Only applies to tables that use the Compressed row format.

Example:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE TABLE_ID = 74 \G
*************************** 1. row ***************************
     TABLE_ID: 74
         NAME: test/t1
         FLAG: 1
       N_COLS: 6
        SPACE: 60
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
1 row in set (0.00 sec)

Notes:

Interpreting the INNODB_SYS_TABLES.FLAG Column Value:

The INNODB_SYS_TABLES.FLAG column provides bit-level information about the table's format and storage characteristics. You can interpret the FLAG column value by adding together the applicable decimal numeric values that are provided in the following table.

Table 21.8 Bit Position Values for Interpreting INNODB_SYS_TABLES FLAG Column Data

Bit PositionDescriptionDecimal Numeric Value
0This bit is set if the row format is not REDUNDANT. In other words, it is set if the row format is COMPACT, DYNAMIC or COMPRESSED.
  • 0 - REDUNDANT

  • 1 - COMPACT, DYNAMIC or COMPRESSED

1-4These four bits contain a small number that represents the compressed page size of the table. The INNODB_SYS_TABLES.ZIP_PAGE_SIZE field also reports the compressed page size, if applicable.
  • 0 - Not Compressed

  • 2 - 1024 Byte Compressed Page Size

  • 4 - 2048 Byte Compressed Page Size

  • 6 - 4096 Byte Compressed Page Size

  • 8 - 8192 Byte Compressed Page Size

  • 10 - 16384 Byte Compressed Page Size

5This bit is set if the row format is DYNAMIC or COMPRESSED.
  • 0 - REDUNDANT or COMPACT

  • 32 - DYNAMIC or COMPRESSED

6This bit is set if the DATA DIRECTORY option is used with CREATE TABLE or ALTER TABLE. This bit is set for file-per-table tablespaces that are located in directories other than the default data directory (datadir). For these tables, a tablename.isl file is present in the same location as the tablename.frm file. The tablename.isl file stores the actual directory path to the tablename.ibd file-per-table tablespace file.
  • 0 - Not a remote file-per-table tablespace

  • 64 - A remote file-per-table tablespace


In the following, table t1 uses ROW_FORMAT=DYNAMIC and has a FLAG value of 33. Based on the information in the preceding table, we can see that bit position 0 would be set to 1, and bit position 5 would be set to 32 for a table with a DYNAMIC row format. These values add up to a FLAG value of 33.

mysql> use test;
Database changed

mysql> SET GLOBAL innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (c1 int) ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 89
         NAME: test/t1
         FLAG: 33
       N_COLS: 4
        SPACE: 75
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
1 row in set (0.01 sec)