Movatterモバイル変換

17.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table

1. Create a simpleInnoDB temporary table:

   mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;

2. QueryINNODB_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

   TheTABLE_ID is a unique identifier for the temporary table. TheNAME 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 becauseInnoDB always creates three hidden table columns (DB_ROW_ID,DB_TRX_ID, andDB_ROLL_PTR).

3. Restart MySQL and queryINNODB_TEMP_TABLE_INFO.

   mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G

   An empty set is returned becauseINNODB_TEMP_TABLE_INFO and its data are not persisted to disk when the server is shut down.

4. Create a new temporary table.

   mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;

5. QueryINNODB_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

   TheSPACE ID may be different because it is dynamically generated when the server is started.