MySQL可能产生内部临时表的几种情况

8.4.4 Internal Temporary Table Use in MySQL
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.
• Evaluation of some views, such those that use the TEMPTABLE algorithm, UNION, or aggregation.
• Evaluation of derived tables (see Section 13.2.11.8, “Derived Tables”).
• Evaluation of common table expressions (see Section 13.2.15, “WITH (Common Table
Expressions)”).
• Tables created for subquery or semijoin materialization (see Section 8.2.2, “Optimizing Subqueries,
Derived Tables, View References, and Common Table Expressions”).
• 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.
• Evaluation of DISTINCT combined with ORDER BY may require a temporary table.
• 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.
• 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. See Section 13.2.6.1, “INSERT ... SELECT Statement”.
1585
Internal Temporary Table Use in MySQL
• Evaluation of multiple-table UPDATE statements.
• Evaluation of GROUP_CONCAT() or COUNT(DISTINCT) expressions.
• Evaluation of window functions (see Section 12.21, “Window Functions”) uses temporary tables as
necessary.