Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.4Mb
PDF (A4) - 37.5Mb
PDF (RPM) - 33.2Mb
HTML Download (TGZ) - 8.3Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.3Mb
Man Pages (TGZ) - 130.1Kb
Man Pages (Zip) - 185.6Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb


MySQL 8.0 Reference Manual  /  ...  /  InnoDB INFORMATION_SCHEMA Temporary Table Info Table

Pre-General Availability Draft: 2018-02-24

15.14.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table

INNODB_TEMP_TABLE_INFO provides information about user-created InnoDB temporary tables that are currently active within the InnoDB instance. It does not provide information about internal InnoDB temporary tables that are used by the optimizer.

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_TEMP%';
+---------------------------------------------+
| Tables_in_INFORMATION_SCHEMA (INNODB_TEMP%) |
+---------------------------------------------+
| INNODB_TEMP_TABLE_INFO                      |
+---------------------------------------------+

For the table definition, see Section 25.32.28, “The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table”.

Example 15.12 INNODB_TEMP_TABLE_INFO

This example demonstrates characteristics of the INNODB_TEMP_TABLE_INFO table.

  1. Create a simple InnoDB temporary table:

    mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
  2. Query INNODB_TEMP_TABLE_INFO to view the temporary table metadata.

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
    *************************** 1. row ***************************
                TABLE_ID: 194
                    NAME: #sql7a79_1_0
                  N_COLS: 4
                   SPACE: 182

    The TABLE_ID is a unique identifier for the temporary table. The NAME column displays the system-generated name for the temporary table, which is prefixed with #sql. The number of columns (N_COLS) is 4 rather than 1 because InnoDB always creates three hidden table columns (DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR).

  3. Restart MySQL and query INNODB_TEMP_TABLE_INFO.

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G

    An empty set is returned because INNODB_TEMP_TABLE_INFO and the data within it are not persisted to disk on server shutdown.

  4. Create a new temporary table.

    mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
  5. Query INNODB_TEMP_TABLE_INFO to view the temporary table metadata.

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
    *************************** 1. row ***************************
                TABLE_ID: 196
                    NAME: #sql7b0e_1_0
                  N_COLS: 4
                   SPACE: 184

    The SPACE ID is new because it is dynamically generated on server restart.



User Comments
Sign Up Login You must be logged in to post a comment.