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  /  ...  /  InnoDB INFORMATION_SCHEMA Temporary Table Info Table

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 24.36.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
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.