In some cases, the server creates internal temporary tables while processing statements. 在某些情况下，服务器在处理语句时创建内部临时表。Users have no direct control over when this occurs.用户无法直接控制何时发生这种情况。
The server creates temporary tables under conditions such as these:服务器在以下条件下创建临时表：
Evaluation of UNION statements, with some exceptions described later.UNION语句的求值，后面将介绍一些例外情况。
Evaluation of some views, such those that use the 对某些视图的求值，例如使用TEMPTABLE algorithm, UNION, or aggregation.TEMPTABLE算法、UNION或聚合的视图。
Evaluation of derived tables (see Section 13.2.11.8, “Derived Tables”).派生表的评估（见第13.2.11.8节，“衍生表”）。
Evaluation of common table expressions (see Section 13.2.15, “WITH (Common Table Expressions)”).通用表表达式的评估（请参阅第13.2.15节，“WITH（通用表表达式）”）。
Tables created for subquery or semijoin materialization (see Section 8.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”).为子查询或半联接具体化创建的表（请参阅第8.2.2节，“优化子查询、派生表、视图引用和公共表表达式”）。
Evaluation of statements that contain an 对包含ORDER BY clause and a different GROUP BY clause, or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.ORDER BY子句和其他GROUP BY子句的语句的求值，或对ORDER BY或GROUP BY包含联接队列中第一个表以外的表中的列的语句的求值。
Evaluation of 对DISTINCT combined with ORDER BY may require a temporary table.DISTINCT和ORDER BY的评估可能需要一个临时表。
For queries that use the 对于使用SQL_SMALL_RESULT modifier, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.SQL_SMALL_RESULT修饰符的查询，MySQL使用内存中的临时表，除非查询还包含需要磁盘存储的元素（稍后描述）。
To evaluate 要评估从同一个表中选择并插入到同一个表中的INSERT ... SELECT statements that select from and insert into the same table, MySQL creates an internal temporary table to hold the rows from the SELECT, then inserts those rows into the target table. INSERT ... SELECT语句，MySQL创建一个内部临时表来保存SELECT中的行，然后将这些行插入到目标表中。See Section 13.2.6.1, “INSERT ... SELECT Statement”.请参阅第13.2.6.1节，“INSERT ... SELECT 语句”。
Evaluation of multiple-table 计算多个表UPDATE statements.UPDATE语句。
Evaluation of 计算GROUP_CONCAT() or COUNT(DISTINCT) expressions.GROUP_CONCAT()或COUNT(DISTINCT)表达式。
Evaluation of window functions (see Section 12.21, “Window Functions”) uses temporary tables as necessary.窗口函数的计算（请参阅第12.21节，“窗口函数”）在必要时使用临时表。
To determine whether a statement requires a temporary table, use 要确定语句是否需要临时表，请使用EXPLAIN and check the Extra column to see whether it says Using temporary (see Section 8.8.1, “Optimizing Queries with EXPLAIN”). EXPLAIN并检查Extra的列，以查看它是否表示使用临时表（请参阅第8.8.1节，“使用EXPLAIN优化查询”）。EXPLAIN does not necessarily say Using temporary for derived or materialized temporary tables. EXPLAIN不一定表示对派生的或物化的临时表使用临时表。For statements that use window functions, 对于使用窗口函数的语句，使用EXPLAIN with FORMAT=JSON always provides information about the windowing steps. FORMAT=JSON的EXPLAIN始终提供有关窗口操作步骤的信息。If the windowing functions use temporary tables, it is indicated for each step.如果窗口功能使用临时表，则每个步骤都会显示临时表。
Some query conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:某些查询条件阻止使用内存中的临时表，在这种情况下，服务器将使用磁盘上的表：
Presence of a 表中是否存在BLOB or TEXT column in the table. BLOB或TEXT列。However, the 然而，从MySQL 8.0.13开始，作为MySQL 8.0中内存内部临时表的默认存储引擎，TempTable storage engine, which is the default storage engine for in-memory internal temporary tables in MySQL 8.0, supports binary large object types as of MySQL 8.0.13. TempTable存储引擎支持二进制大对象类型。See Internal Temporary Table Storage Engine.请参阅内部临时表存储引擎。
Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the 如果使用SELECT list, if UNION or UNION ALL is used.UNION或UNION ALL，则SELECT列表中存在最大长度大于512（二进制字符串为字节，非二进制字符串为字符）的任何字符串列。
The SHOW COLUMNS and DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an on-disk table.SHOW COLUMNS语句和DESCRIBE语句使用BLOB作为某些列的类型，因此用于结果的临时表是一个磁盘表。
The server does not use a temporary table for 对于满足某些限定条件的UNION statements that meet certain qualifications. UNION语句，服务器不使用临时表。Instead, it retains from temporary table creation only the data structures necessary to perform result column typecasting. 相反，它从临时表创建中只保留执行结果列类型转换所需的数据结构。The table is not fully instantiated and no rows are written to or read from it; rows are sent directly to the client. 该表没有完全实例化，没有写入或读取任何行；行直接发送到客户端。The result is reduced memory and disk requirements, and smaller delay before the first row is sent to the client because the server need not wait until the last query block is executed. 结果是减少了内存和磁盘需求，并且在将第一行发送到客户机之前的延迟更小，因为服务器不需要等到执行最后一个查询块。EXPLAIN and optimizer trace output reflects this execution strategy: The UNION RESULT query block is not present because that block corresponds to the part that reads from the temporary table.EXPLAIN和优化器跟踪输出反映了这种执行策略：联合结果查询块不存在，因为该块对应于从临时表读取的部分。
These conditions qualify a 这些条件使UNION for evaluation without a temporary table:UNION有资格在没有临时表的情况下进行评估：
The union is 联合是UNION ALL, not UNION or UNION DISTINCT.UNION ALL，而不是UNION或UNION DISTINCT。
There is no global 没有全局ORDER BY clause.ORDER BY子句。
The union is not the top-level query block of an 联合不是{INSERT | REPLACE} ... SELECT ... statement.{INSERT | REPLACE} ... SELECT ...语句的顶级查询块
An internal temporary table can be held in memory and processed by the 内部临时表可以保存在内存中，并由TempTable or MEMORY storage engine, or stored on disk by the InnoDB storage engine.TempTable或MEMORY存储引擎处理，或者由InnoDB存储引擎存储在磁盘上。
The internal_tmp_mem_storage_engine session variable defines the storage engine for in-memory internal temporary tables. internal_tmp_mem_storage_engine会话变量定义内存内部临时表的存储引擎。Permitted values are 允许的值是TempTable (the default) and MEMORY.TempTable（默认值）和MEMORY。
The TempTable storage engine provides efficient storage for VARCHAR and VARBINARY columns, and other binary large object types as of MySQL 8.0.13.TempTable存储引擎为VARCHAR和VARBINARY列以及MySQL 8.0.13中的其他二进制大对象类型提供了高效的存储。
The temptable_max_ram variable defines the maximum amount of RAM that can be occupied by the TempTable storage engine before it starts allocating space from disk in the form memory-mapped temporary files or InnoDB on-disk internal temporary tables. temptable_max_ram变量定义了诱惑存储引擎在开始以内存映射临时文件或InnoDB on disk内部临时表的形式从磁盘分配空间之前可以占用的最大RAM量。The default 默认temptable_max_ram setting is 1GiB. temptable_max_ram设置为1GiB。The temptable_use_mmap variable (introduced in MySQL 8.0.16; deprecated in MySQL 8.0.26) controls whether the TempTable storage engine uses memory-mapped files or InnoDB on-disk internal temporary tables when the temptable_max_ram limit is exceeded. temptable_use_mmap变量（在MySQL 8.0.16中引入；MySQL 8.0.26中已弃用）控制当超过temptable_max_ram限制时，TempTable存储引擎是使用内存映射文件还是使用磁盘上的InnoDB内部临时表。The default setting is 默认设置是temptable_use_mmap=ON. temptable_use_mmap=ON。The MySQL 8.0.23中引入的temptable_max_mmap variable, introduced in MySQL 8.0.23, defines the maximum amount of memory the TempTable storage engine is permitted to allocate from memory-mapped files before it starts storing internal temporary table data to InnoDB on-disk internal temporary tables. temptable_max_mmap变量定义了TempTable存储引擎在开始将内部临时表数据存储到磁盘上的InnoDB内部临时表之前，允许从内存映射文件中分配的最大内存量。A temptable_max_mmap=0 setting disables allocation from memory-mapped files, effectively disabling their use, regardless of the temptable_use_mmap setting.temptable_max_mmap=0设置会禁用内存映射文件的分配，从而有效禁用它们的使用，而不管temptable_use_mmap设置如何。
The 从MySQL 8.0.26开始，不推荐使用temptable_use_mmap variable is deprecated as of MySQL 8.0.26; expect support for it to be removed in a future version of MySQL. temptable_use_mmap变量；希望在MySQL的未来版本中删除对它的支持。Setting 设置temptable_max_mmap=0 is equivalent to setting temptable_use_mmap=OFF.temptable_max_mmap=0相当于设置temptable_use_mmap=OFF。
The temptable_max_ram setting does not account for the thread-local memory block allocated to each thread that uses the TempTable storage engine. temptable_max_ram设置不考虑分配给使用TempTable存储引擎的每个线程的线程本地内存块。The size of the thread-local memory block depends on the size of the thread's first memory allocation request. 线程本地内存块的大小取决于线程的第一个内存分配请求的大小。If the request is less than 1MB, which it is in most cases, the thread-local memory block size is 1MB. If the request is greater than 1MB, the thread-local memory block is approximately the same size as the initial memory request. 如果请求小于1MB，在大多数情况下，线程本地内存块大小为1MB。如果请求大于1MB，则线程本地内存块的大小与初始内存请求的大小大致相同。The thread-local memory block is held in thread-local storage until thread exit.线程本地内存块保存在线程本地存储器中，直到线程退出。
Use of memory-mapped temporary files by the TempTable storage engine as an overflow mechanism for internal temporary tables is governed by these rules:TempTable存储引擎将内存映射的临时文件用作内部临时表的溢出机制受以下规则控制：
Temporary files are created in the directory defined by the 临时文件在tmpdir variable.tmpdir变量定义的目录中创建。
Temporary files are deleted immediately after they are created and opened, and therefore do not remain visible in the 临时文件在创建和打开后立即被删除，因此在tmpdir directory. tmpdir目录中不可见。The space occupied by temporary files is held by the operating system while temporary files are open. 临时文件占用的空间在临时文件打开时由操作系统保留。The space is reclaimed when temporary files are closed by the 当TempTable storage engine, or when the mysqld process is shut down.TempTable存储引擎关闭临时文件时，或者当mysqld进程关闭时，会回收空间。
Data is never moved between RAM and temporary files, within RAM, or between temporary files.数据永远不会在RAM和临时文件之间、RAM内或临时文件之间移动。
New data is stored in RAM if space becomes available within the limit defined by 如果空间在temptable_max_ram. temptable_max_ram定义的限制范围内可用，则新数据存储在RAM中。Otherwise, new data is stored in temporary files.否则，新数据将存储在临时文件中。
If space becomes available in RAM after some of the data for a table is written to temporary files, it is possible for the remaining table data to be stored in RAM.如果一个表的某些数据写入临时文件后，RAM中的空间变为可用，则剩余的表数据可能存储在RAM中。
If the 如果TempTable storage engine is configured to use InnoDB on-disk internal temporary tables as the overflow mechanism (temptable_use_mmap=OFF or temptable_max_mmap=0), an in-memory table that exceeds the temptable_max_ram limit is converted to an InnoDB on-disk internal temporary table, and any rows belonging to that table are moved from memory to the InnoDB on-disk internal temporary table. TempTable存储引擎配置为使用InnoDB磁盘上内部临时表作为溢出机制（temptable_use_mmap=OFF或temptable_max_mmap=0），则超过temptable_max_ram限制的内存中表将转换为InnoDB磁盘上内部临时表，并且属于该表的任何行都将从内存移动到磁盘上的InnoDB内部临时表。The internal_tmp_disk_storage_engine setting (removed in MySQL 8.0.16) has no affect on the TempTable storage engine overflow mechanism.internal_tmp_disk_storage_engine设置（在MySQL 8.0.16中删除）对TempTable存储引擎溢出机制没有影响。
Prior to MySQL 8.0.23, 在MySQL 8.0.23之前，如果InnoDB on-disk internal temporary tables are recommended as the TempTable overflow mechanism if the TempTable storage engine often exceeds the temptable_max_ram limit and uses excessive space in the temporary directory for memory-mapped files. TempTable存储引擎经常超过temptable_max_ram限制，并在临时目录中为内存映射文件使用过多空间，则建议使用InnoDB磁盘上内部临时表作为TempTable溢出机制。As of MySQL 8.0.23, the 从MySQL 8.0.23开始，temptable_max_mmap variable defines a limit on the amount of memory the TempTable storage engine allocates from memory-mapped files, which addresses the risk of those files using too much space. temptable_max_mmap变量定义了诱惑式存储引擎从内存映射文件分配的内存量的限制，这解决了这些文件使用过多空间的风险。Exceeding the 由于使用大型内部临时表或大量使用内部临时表，通常会超过temptable_max_ram limit typically occurs due to use of large internal temporary tables or extensive use of internal temporary tables. temptable_max_ram限制。InnoDB on-disk internal temporary tables are created in session temporary tablespaces, which reside in the data directory by default. InnoDB磁盘上的内部临时表是在会话临时表空间中创建的，默认情况下，会话临时表空间位于数据目录中。For more information, see Section 15.6.3.5, “Temporary Tablespaces”.有关更多信息，请参阅第15.6.3.5节，“临时表空间”。
When using the 当使用MEMORY storage engine for in-memory temporary tables, MySQL automatically converts an in-memory temporary table to an on-disk table if it becomes too large. MEMORY存储引擎处理内存中的临时表时，如果内存中的临时表太大，MySQL会自动将其转换为磁盘上的临时表。The maximum size of an in-memory temporary table is defined by the 内存中临时表的最大大小由tmp_table_size or max_heap_table_size value, whichever is smaller. tmp_table_size或max_heap_table_size值定义，以较小者为准。This differs from 这与使用MEMORY tables explicitly created with CREATE TABLE. CREATE TABLE显式创建的MEMORY表不同。For such tables, only the 对于这样的表，只有max_heap_table_size variable determines how large a table can grow, and there is no conversion to on-disk format.max_heap_table_size变量确定表可以增长多大，并且没有到磁盘格式的转换。
Starting with MySQL 8.0.16, the server always uses the 从MySQL 8.0.16开始，服务器总是使用InnoDB storage engine for managing internal temporary tables on disk.InnoDB存储引擎来管理磁盘上的内部临时表。
In MySQL 8.0.15 and earlier, the 在MySQL 8.0.15及更早版本中，internal_tmp_disk_storage_engine variable was used to define the storage engine used for on-disk internal temporary tables. internal_tmp_disk_storage_engine变量用于定义用于磁盘内部临时表的存储引擎。This variable was removed in MySQL 8.0.16, and the storage engine used for this purpose is no longer user-configurable.MySQL 8.0.16中删除了此变量，用于此目的的存储引擎不再是用户可配置的。
In MySQL 8.0.15 and earlier: For common table expressions (CTEs), the storage engine used for on-disk internal temporary tables cannot be 在MySQL 8.0.15及更早版本中：对于公共表表达式（CTE），用于磁盘内部临时表的存储引擎不能是MyISAM. MyISAM。If 如果internal_tmp_disk_storage_engine=MYISAM, an error occurs for any attempt to materialize a CTE using an on-disk temporary table.internal_tmp_disk_storage_engine=MYISAM，则任何使用磁盘上临时表具体化CTE的尝试都会发生错误。
In MySQL 8.0.15 and earlier: When using 在MySQL 8.0.15及更早版本中：当使用internal_tmp_disk_storage_engine=INNODB, queries that generate on-disk internal temporary tables that exceed InnoDB row or column limits return Row size too large or Too many columns errors. internal_tmp_disk_storage_engine=INNODB时，在磁盘上生成超过InnoDB行或列限制的内部临时表的查询会返回行大小过大或列过多的错误。The workaround is to set 变通的解决方法是将internal_tmp_disk_storage_engine to MYISAM.internal_tmp_disk_storage_engine设置为MYISAM。
When in-memory internal temporary tables are managed by the 当内存中的内部临时表由TempTable storage engine, rows that include VARCHAR columns, VARBINARY columns, and other binary large object type columns (supported as of MySQL 8.0.13) are represented in memory by an array of cells, with each cell containing a NULL flag, the data length, and a data pointer. TempTable存储引擎管理时，包含VARCHAR列、VARBINARY列和其他二进制大对象类型列（MySQL 8.0.13支持）的行在内存中由一个单元格数组表示，每个单元格包含一个空标志、数据长度和一个数据指针。Column values are placed in consecutive order after the array, in a single region of memory, without padding. 列值按连续顺序放置在数组之后，在单个内存区域中，无需填充。Each cell in the array uses 16 bytes of storage. 阵列中的每个单元使用16字节的存储空间。The same storage format applies when the 当TempTable storage engine exceeds the temptable_max_ram limit and starts allocating space from disk as memory-mapped files or InnoDB on-disk internal temporary tables.TempTable存储引擎超过temptable_max_ram限制，并开始从磁盘分配空间作为内存映射文件或InnoDB on-disk内部临时表时，同样的存储格式适用。
When in-memory internal temporary tables are managed by the 当内存中的内部临时表由MEMORY storage engine, fixed-length row format is used. MEMORY存储引擎管理时，将使用固定长度的行格式。VARCHAR and VARBINARY column values are padded to the maximum column length, in effect storing them as CHAR and BINARY columns.VARCHAR和VARBINARY列值被填充到最大列长度，实际上将它们存储为CHAR和BINARY列。
Previous to MySQL 8.0.16, on-disk internal temporary tables were managed by the 在MySQL 8.0.16之前，磁盘上的内部临时表由InnoDB or MyISAM storage engine (depending on the internal_tmp_disk_storage_engine setting). InnoDB或MyISAM存储引擎管理（取决于internal_tmp_disk_storage_engine设置）。Both engines store internal temporary tables using dynamic-width row format. Columns take only as much storage as needed, which reduces disk I/O, space requirements, and processing time compared to on-disk tables that use fixed-length rows. 两个引擎都使用动态宽度行格式存储内部临时表。列只占用所需的存储空间，与使用固定长度行的磁盘表相比，这减少了磁盘I/O、空间需求和处理时间。Beginning with MySQL 8.0.16, 从MySQL 8.0.16开始，不支持internal_tmp_disk_storage_engine is not supported, and internal temporary tables on disk are always handled by InnoDB.internal_tmp_disk_storage_engine，磁盘上的内部临时表总是由InnoDB处理。
When using the 使用MEMORY storage engine, statements can initially create an in-memory internal temporary table and then convert it to an on-disk table if the table becomes too large. MEMORY存储引擎时，语句最初可以创建内存中的内部临时表，如果表太大，则可以将其转换为磁盘上的表。In such cases, better performance might be achieved by skipping the conversion and creating the internal temporary table on disk to begin with. 在这种情况下，可以通过跳过转换并首先在磁盘上创建内部临时表来实现更好的性能。The big_tables variable can be used to force disk storage of internal temporary tables.big_tables变量可用于强制内部临时表的磁盘存储。
When an internal temporary table is created in memory or on disk, the server increments the 在内存或磁盘上创建内部临时表时，服务器会增加Created_tmp_tables value. Created_tmp_tables值。When an internal temporary table is created on disk, the server increments the 在磁盘上创建内部临时表时，服务器会增加Created_tmp_disk_tables value. Created_tmp_disk_tables值。If too many internal temporary tables are created on disk, consider increasing the 如果在磁盘上创建了太多的内部临时表，请考虑增加tmp_table_size and max_heap_table_size settings.tmp_table_size和max_heap_table_size设置。
Due to a known limitation, 由于已知的限制，Created_tmp_disk_tables does not count on-disk temporary tables created in memory-mapped files. Created_tmp_disk_tables不依赖于在内存映射文件中创建的磁盘临时表。By default, the TempTable storage engine overflow mechanism creates internal temporary tables in memory-mapped files. 默认情况下，诱人的存储引擎溢出机制会在内存映射文件中创建内部临时表。This behavior is controlled by the 此行为由temptable_use_mmap and temptable_max_mmap variables.temptable_use_mmap和temptable_max_mmap变量控制。
The memory/temptable/physical_ram and memory/temptable/physical_disk Performance Schema instruments can be used to monitor TempTable space allocation from memory and disk. memory/temptable/physical_ram和memory/temptable/physical_disk性能模式工具可用于监控内存和磁盘的可诱惑空间分配。memory/temptable/physical_ram reports the amount of allocated RAM. memory/temptable/physical_ram报告分配的内存量。memory/temptable/physical_disk reports the amount of space allocated from disk when memory-mapped files are used as the TempTable overflow mechanism. memory/temptable/physical_disk报告当内存映射文件用作tentable溢出机制时从磁盘分配的空间量。If the 如果physical_disk instrument reports a value other than 0 and memory-mapped files are used as the TempTable overflow mechanism, the temptable_max_ram threshold was reached at some point. physical_disk器材械报告的值不是0，并且内存映射文件被用作TempTable溢出机制，则在某个时候达到了temptable_max_ram阈值。Data can be queried in Performance Schema memory summary tables such as 可以在性能模式内存摘要表中查询数据，例如memory_summary_global_by_event_name. memory_summary_global_by_event_name。See Section 27.12.20.10, “Memory Summary Tables”.请参阅第27.12.20.10节，“内存汇总表”。