SQL 临时表

第 9.4 课 · 阅读时间：约 7 分钟

SQL 临时表是一种只在当前会话（连接）中存在、会话结束时自动删除的表。在本课中，你将学习如何创建临时表、如何用查询结果填充它、它与普通表和 CTE 有何不同，以及它在 MySQL 中有哪些限制。

复杂的分析通常分步骤进行：先选出关注的客户，再针对他们计算多份报表。中间结果可以在每份报表中以子查询或 CTE 的形式重复书写，也可以一次性保存到临时表中，供后续所有查询重复使用。

SQLtest 上的示例数据库是只读的，因此在那里执行 CREATE TEMPORARY TABLE 会返回权限错误。示例使用 Sakila 模式。

什么是临时表

临时表的外观和用法与普通表相同：可以插入、查询、更新和删除行。区别在于它的生命周期和可见范围：

• 它只在创建它的会话中可见；其他连接看不到它，即使它们创建了同名的临时表；
• 会话结束时它会被自动删除；
• 它用于存放中间数据，而不是永久存储。

语法

CREATE TEMPORARY TABLE table_name (
    column1 data_type,
    column2 data_type
);

最常见的做法是直接根据查询创建临时表：

CREATE TEMPORARY TABLE table_name AS
SELECT ...;

示例：重复使用中间结果

保存付款次数超过 30 次的客户：

CREATE TEMPORARY TABLE tmp_active_customers AS
SELECT customer_id, COUNT(*) AS payment_count
FROM payment
GROUP BY customer_id
HAVING COUNT(*) > 30;

现在，多个独立的查询都可以使用它，而无需重复聚合：

SELECT ac.customer_id, c.first_name, c.last_name, ac.payment_count
FROM tmp_active_customers AS ac
INNER JOIN customer AS c ON c.customer_id = ac.customer_id
ORDER BY ac.payment_count DESC;

SELECT COUNT(*) AS customers,
       AVG(payment_count) AS avg_payments,
       MAX(payment_count) AS max_payments
FROM tmp_active_customers;

结果：最活跃客户的列表，以及同一组客户的汇总数据，该组只计算了一次。

在较长的会话中不再需要这张表时，请显式删除它：

DROP TEMPORARY TABLE IF EXISTS tmp_active_customers;

在 MySQL 中，DROP TEMPORARY TABLE 只会删除临时表，因此不会误删同名的普通表。

临时表与普通表

最后一行是个陷阱：如果临时表与某张已有的表同名，它会在本会话剩余的时间里遮蔽那张表。请使用 tmp_ 之类的前缀。

临时表与 CTE

当中间结果只在一个查询中需要时，使用 CTE（第 6.4 课）。当中间结果需要在多个查询中使用、计算代价高，或者数据量大、可以从索引中受益时，使用临时表：

CREATE TEMPORARY TABLE tmp_action_customers AS
SELECT DISTINCT r.customer_id
FROM rental AS r
INNER JOIN inventory AS i ON i.inventory_id = r.inventory_id
INNER JOIN film_category AS fc ON fc.film_id = i.film_id
INNER JOIN category AS c ON c.category_id = fc.category_id
WHERE c.name = 'Action';

ALTER TABLE tmp_action_customers ADD PRIMARY KEY (customer_id);

MySQL 中的限制

• 每个查询只能引用一次。 MySQL 不允许临时表在同一个查询中出现两次，例如在自连接中，或同时出现在外层查询和子查询中：会报错“Can't reopen table”。这种情况下请使用 CTE 或普通表。
• 复制和连接池。 在复用连接的应用中，临时表可能会保留到同一连接上的下一个请求。用完后请显式删除。
• 存储引擎。 临时表根据大小存放在内存或磁盘上。ENGINE = MEMORY 会把表保存在内存中，但不支持 TEXT/BLOB 列，并受 max_heap_table_size 限制。

要点：

• 临时表存在到会话结束，并且只对该会话可见。
• CREATE TEMPORARY TABLE ... AS SELECT 保存查询的结果。
• 临时表可以被许多查询重复使用，也可以建立索引；CTE 只存在于一条语句内部。
• 使用 tmp_ 前缀：临时表会遮蔽同名的普通表。
• 在 MySQL 中，一个查询只能引用同一张临时表一次。

常见问题

临时表什么时候被删除？

在创建它的会话结束时。你也可以提前删除它：在 MySQL 中使用 DROP TEMPORARY TABLE table_name，在其他数据库中使用 DROP TABLE table_name。

临时表和 CTE 有什么区别？

CTE 只存在于一条 SQL 语句内部。临时表是一张真正的表，存在到会话结束，因此多个独立的查询都可以使用它，而且它可以有索引。

其他用户能看到我的临时表吗？

不能。临时表只在创建它的会话中可见。两个会话甚至可以拥有同名的临时表而不会产生任何冲突。