Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 43.3Mb
PDF (A4) - 43.4Mb
Man Pages (TGZ) - 297.2Kb
Man Pages (Zip) - 402.4Kb
Info (Gzip) - 4.3Mb
Info (Zip) - 4.3Mb
Excerpts from this Manual

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

28.4.23 The INFORMATION_SCHEMA INNODB_TABLES Table

TheINNODB_TABLES table provides metadata aboutInnoDB tables.

For related usage information and examples, seeSection 17.15.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.

TheINNODB_TABLES table has these columns:

  • TABLE_ID

    An identifier for theInnoDB 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 thelower_case_table_names setting.

  • FLAG

    A numeric value that represents bit-level information about table format and storage characteristics.

  • N_COLS

    The number of columns in the table. The number reported includes three hidden columns that are created byInnoDB (DB_ROW_ID,DB_TRX_ID, andDB_ROLL_PTR). The number reported also includesvirtual generated columns, if present.

  • SPACE

    An identifier for the tablespace where the table resides. 0 means theInnoDBsystem tablespace. Any other number represents either afile-per-table tablespace or a general tablespace. This identifier stays the same after aTRUNCATE 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, orCompressed).

  • ZIP_PAGE_SIZE

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

  • SPACE_TYPE

    The type of tablespace to which the table belongs. Possible values includeSystem for the system tablespace,General for general tablespaces, andSingle for file-per-table tablespaces. Tables assigned to the system tablespace usingCREATE TABLE orALTER TABLETABLESPACE=innodb_system have aSPACE_TYPE ofGeneral. For more information, seeCREATE TABLESPACE.

  • INSTANT_COLS

    The number of columns that existed before the first instant column was added usingALTER TABLE ... ADD COLUMN withALGORITHM=INSTANT. This column is no longer used as of MySQL 8.0.29 but continues to show information for tables with columns that were added instantly prior to MySQL 8.0.29.

  • TOTAL_ROW_VERSIONS

    The number of row versions for the table. The initial value is 0. The value is incremented byALTER TABLE ... ALGORITHM=INSTANT operations that add or remove columns. When a table with instantly added or dropped columns is rebuilt due to a table-rebuildingALTER TABLE orOPTIMIZE TABLE operation, the value is reset to 0. For more information, seeColumn Operations.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TABLE_ID = 214\G*************************** 1. row ***************************          TABLE_ID: 1064              NAME: test/t1              FLAG: 33            N_COLS: 6             SPACE: 3        ROW_FORMAT: Dynamic     ZIP_PAGE_SIZE: 0        SPACE_TYPE: Single      INSTANT_COLS: 0TOTAL_ROW_VERSIONS: 3

Notes

  • You must have thePROCESS privilege to query this table.

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