Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.1Mb
PDF (A4) - 38.1Mb
PDF (RPM) - 33.0Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 132.7Kb
Man Pages (Zip) - 189.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  The INFORMATION_SCHEMA INNODB_TABLES Table

24.36.24 The INFORMATION_SCHEMA INNODB_TABLES Table

The INNODB_TABLES table provides metadata about InnoDB tables.

For related usage information and examples, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.

The INNODB_TABLES table has these columns:

  • TABLE_ID

    An identifier for the InnoDB table. This value is unique across all databases in the instance.

  • NAME

    The name of the table, preceded by the schema (database) name where appropriate; for example test/t1. 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.

  • FLAG

    Bit-level data about table format and storage characteristics including row format, compressed page size (if applicable), and whether the DATA DIRECTORY clause was used with CREATE TABLE or ALTER TABLE. For details, see Interpreting the INNODB_TABLES.FLAG Column Value.

  • N_COLS

    The 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). The number reported also includes virtual generated columns, if present.

  • SPACE

    An identifier for the tablespace where the table resides. 0 means the InnoDB system tablespace. Any other number represents either a file-per-table tablespace or a general tablespace. This identifier stays the same after a TRUNCATE TABLE statement. For file-per-table tablespaces, this identifier is unique for tables across all databases in the instance.

  • ROW_FORMAT

    The table's row format (Compact, Redundant, Dynamic, or Compressed).

  • ZIP_PAGE_SIZE

    The zip page size. Applies only to tables with a row format of Compressed.

  • SPACE_TYPE

    The type of tablespace to which the table belongs. Possible values include System for the system tablespace, General for general tablespaces, and Single for file-per-table tablespaces. Tables assigned to the system tablespace using CREATE TABLE or ALTER TABLE TABLESPACE=innodb_system have a SPACE_TYPE of General. For more information, see CREATE TABLESPACE.

  • INSTANT_COLS

    The number of columns in the table prior to adding the first instant column using ALTER TABLE ... ADD COLUMN with ALGORITHM=INSTANT.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TABLE_ID = 214\G
*************************** 1. row ***************************
     TABLE_ID: 214
         NAME: test/t1
         FLAG: 129
       N_COLS: 4
        SPACE: 233
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: General
 INSTANT_COLS: 0

Notes

  • You must have the PROCESS privilege to query this table.

  • Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.

Interpreting the INNODB_TABLES.FLAG Column Value

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

Table 24.6 Bit Position Values for Interpreting INNODB_TABLES.FLAG Column Data

Bit Position Description Decimal Numeric Value
0 This 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-4 These four bits contain a small number that represents the compressed page size of the table. The INNODB_TABLES.ZIP_PAGE_SIZE column 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

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

  • 32 = DYNAMIC or COMPRESSED

6 This 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).
  • 0 = Is not a remote file-per-table tablespace

  • 64 = Is a remote file-per-table tablespace

7 This bit is set if the table is assigned to a shared tablespace (either a general tablespace or a system tablespace) using the CREATE TABLE or ALTER TABLE TABLESPACE=tablespace_name option.
  • 0 = Table is located in a default location depending on the value of the innodb_file_per_table.

  • 128 = The table is explicitly assigned to a shared 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> CREATE TABLE t1 (c1 int) ROW_FORMAT=DYNAMIC;

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1'\G
*************************** 1. row ***************************
     TABLE_ID: 89
         NAME: test/t1
         FLAG: 33
       N_COLS: 4
        SPACE: 75
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
 INSTANT_COLS: 0

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.