MySQL :: MySQL 8.0 Reference Manual :: 24.31.23 The INFORMATION_SCHEMA INNODB_SYS

version 8.0

5.7 current
5.6

5.6 Japanese

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

Pre-General Availability Draft: 2017-07-17

24.31.23 The INFORMATION_SCHEMA INNODB_SYS_TABLES Table

The INNODB_SYS_TABLES table provides metadata about InnoDB tables.

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

Table 24.23 INNODB_SYS_TABLES Columns

Column name	Description
`TABLE_ID`	An identifier for each `InnoDB` table that is unique across all databases in the instance.
`NAME`	The 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.
`FLAG`	This 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_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. Only applies to tables that use the Compressed row format.
`SPACE_TYPE`	The type of tablespace to which the table belongs. Possible values include `System` (for the `InnoDB` system tablespace), `General` (for `InnoDB` general tablespaces created using `CREATE TABLESPACE`, and `Single` (for `InnoDB` file-per-table tablespaces). Tables assigned to the system tablespace using the `CREATE TABLE` or `ALTER TABLE` `TABLESPACE=innodb_system` clause have a `General` `SPACE_TYPE`.

Example:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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

Notes:

Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.
You must have the PROCESS privilege to query this table.

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 24.24 Bit Position Values for Interpreting INNODB_SYS_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_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
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 - Not a remote file-per-table tablespace 64 - 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;
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
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
1 row in set (0.00 sec)

PREV HOME UP NEXT

User Comments