content_image/864900.jpg
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 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. 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 may be different because it is dynamically generated when the server is started. 

## 15.15.8 Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES 

The INFORMATION_SCHEMA.FILES  table provides metadata about all InnoDB  tablespace types including file-per-table tablespaces, general tablespaces, the system tablespace, temporary table tablespaces, and undo tablespaces (if present). 

This section provides InnoDB -specific usage examples. For more information about data provided by the INFORMATION_SCHEMA.FILES  table, see Section 26.3.15, “The INFORMATION_SCHEMA FILES Table” . 

<img src='content_image/864900.jpg'>

## Note 

The INNODB_TABLESPACES  and INNODB_DATAFILES  tables also provide metadata about InnoDB  tablespaces, but data is limited to file-per-table, general, and undo tablespaces. 

This query retrieves metadata about the InnoDB  system tablespace from fields of the INFORMATION_SCHEMA.FILES  table that are pertinent to InnoDB  tablespaces. INFORMATION_SCHEMA.FILES  fields that are not relevant to InnoDB  always return NULL, and are excluded from the query. 

