MySQL :: MySQL 8.4 参考手册 :: 17.15.7 InnoDB INFORMATION

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 17 InnoDB 存储引擎 / 17.15 InnoDB INFORMATION_SCHEMA 表 / 17.15.7 InnoDB INFORMATION_SCHEMA 临时表信息表

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

17.15.7 InnoDB INFORMATION_SCHEMA 临时表信息表17.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table

INNODB_TEMP_TABLE_INFO 提供有关在 InnoDB 实例中活动的用户创建的 InnoDB 临时表的信息。它不提供有关优化器使用的内部 InnoDB 临时表的信息。INNODB_TEMP_TABLE_INFO provides information about user-created InnoDB temporary tables that are active in the InnoDB instance. It does not provide information about internal InnoDB temporary tables used by the optimizer.

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

有关表定义，请参见第 28.4.27 节，“INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO 表”。For the table definition, see Section 28.4.27, “The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table”.

示例 17.12 INNODB_TEMP_TABLE_INFOExample 17.12 INNODB_TEMP_TABLE_INFO

此示例演示了 INNODB_TEMP_TABLE_INFO 表的特性。This example demonstrates characteristics of the INNODB_TEMP_TABLE_INFO table.

创建一个简单的 InnoDB 临时表：Create a simple InnoDB temporary table:
```
mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
```
查询 INNODB_TEMP_TABLE_INFO 查看临时表的元数据。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
```
TABLE_ID 是临时表的唯一标识符。NAME 列显示临时表的系统生成名称，该名称以 “#sql” 为前缀。列数（N_COLS）为 4 而非 1，因为 InnoDB 始终创建三个隐藏表列（DB_ROW_ID、DB_TRX_ID 和 DB_ROLL_PTR）。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).
重启 MySQL 并查询 INNODB_TEMP_TABLE_INFO。Restart MySQL and query INNODB_TEMP_TABLE_INFO.
```
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
```
返回空集是因为INNODB_TEMP_TABLE_INFO及其数据在服务器关闭时不会持久化到磁盘。An empty set is returned because INNODB_TEMP_TABLE_INFO and its data are not persisted to disk when the server is shut down.

创建一个新的临时表。Create a new temporary table.

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

查询 INNODB_TEMP_TABLE_INFO 查看临时表的元数据。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
```
SPACE ID 可能不同，因为它是在服务器启动时动态生成的。The SPACE ID may be different because it is dynamically generated when the server is started.

首页上一级下一节