课程 9.3: 临时表

在上一课中，我们讨论了如何使用 CREATE TABLE 创建表。现在让我们来看一种特殊类型的表：临时表。它们帮助在会话或事务中存储中间数据，通常用于分析查询、ETL 过程和多步骤数据处理。

与常规表不同，临时表并不用于永久数据存储。它们在有限的时间内创建，然后在会话结束后自动删除或变得不可用。

什么是临时表

临时表是为短期数据存储而创建的表，用户在工作或脚本运行时使用。

这些表通常：

• 仅在当前连接或事务中可用；
• 用于中间计算；
• 有助于将复杂逻辑拆分为几个更清晰的步骤；
• 在需要在多个查询中重用中间结果时非常有用。

在许多数据库管理系统中，临时表是使用 TEMPORARY 或 TEMP 关键字创建的。

基本语法

创建临时表的一种常见方式如下所示：

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

之后，您可以像使用常规表一样使用临时表：插入数据、选择数据、更新行和删除行。

示例：创建临时表

假设我们想存储支付超过 30 次的客户列表：

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

现在我们可以在后续查询中使用这个临时表：

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

结果：我们得到了一个活跃客户的列表，并且可以重用已经准备好的数据集，而无需重新运行原始聚合。

临时表与常规表的区别

尽管临时表和常规表在结构上相似，但有几个重要的区别。

1. 生命周期

• 常规表在数据库中永久存在，直到您显式删除它。
• 临时表存在的时间有限，通常直到会话或事务结束。

2. 目的

• 常规表用于永久存储业务数据。
• 临时表用于中间、技术或准备数据。

3. 可见性范围

• 常规表对所有具有所需权限的用户可用。
• 临时表通常仅在当前连接中可见。

4. 实际用途

• 常规表存储客户、订单、产品、支付和其他核心信息。
• 临时表存储中间过滤、聚合或报告准备的结果。

临时表特别有用的情况

在以下情况下使用临时表：

• 查询过于复杂，更容易拆分为多个阶段；
• 需要多次使用相同的中间结果；
• 需要临时存储清理或聚合的数据；
• 想要简化 SQL 脚本的可读性和维护性。

例如，您可以首先构建一个包含选定电影的临时表，然后仅为它们计算指标。

CREATE TEMPORARY TABLE expensive_films AS
SELECT film_id, title, rental_rate
FROM film
WHERE rental_rate >= 4.00;

SELECT COUNT(*) AS film_count, AVG(rental_rate) AS avg_rate
FROM expensive_films;

结果：逻辑被拆分为两个清晰的步骤，数据准备和分析。

临时表与常规 CTE

在某些情况下，您可以使用 CTE（WITH）代替临时表。区别在于：

• CTE 仅在单个查询中存在；
• 临时表可以在会话期间的多个查询中使用；
• CTE 适合在一个 SQL 语句中紧凑的逻辑；
• 临时表在需要重用中间结果时更方便。

如果结果只需要一次，CTE 通常更简单。如果需要跨多个步骤，临时表通常更方便。

注意事项

在使用临时表时，记住以下几点是有用的：

• 不要在一个简单查询足够的地方使用它们；
• 给临时表起一个清晰的名称，反映其目的；
• 注意在您的数据库管理系统中何时删除表；
• 不要在临时表中保留数据超过必要的时间；
• 检查您数据库管理系统中的语法细节，因为 TEMPORARY TABLE 的行为可能会有所不同。

使用得当，临时表可以使复杂的 SQL 更加可读和可管理。

实际示例

想象一下，我们需要找到租赁了 动作 类别电影的客户，然后为他们构建一个单独的报告。

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

SELECT ac.customer_id, cu.first_name, cu.last_name
FROM action_customers ac
JOIN customer cu ON ac.customer_id = cu.customer_id
ORDER BY cu.last_name, cu.first_name;

这种方法特别方便，如果在这个列表之后，您需要运行几个额外的分析查询。

本课的关键要点：

• 临时表用于短期存储中间数据。
• 它们通常仅在当前会话或事务中存在。
• 在语法和用法上，它们与常规表相似，但不用于永久数据存储。
• 临时表在复杂的多步骤查询和分析场景中特别有用。
• 如果中间结果只在一个查询中需要，CTE 可能是更好的选择。

在下一课中，我们将看看临时表与视图的区别，以及何时使用这些工具更好。