sql-temporary-tables

SQL - 临时表

临时表是特殊的表，它们仅在数据库会话或事务期间存在。它们对于存储中间结果、简化复杂查询以及隔离数据操作而不会影响永久表非常有用。

1. 什么是临时表以及为何使用它们？

临时表，或称“temp table”，是在数据库的临时存储区域（如 SQL Server 中的 tempdb）中创建的表。您可以在它们上执行标准的 SQL 操作，如 SELECT、INSERT、UPDATE、DELETE 和 JOIN，就像常规表一样。

常见的用例包括：

• 存储中间结果：在多步骤过程中，您可以将第一步的结果存储在临时表中，然后在后续步骤中查询它。这可以提高可读性，有时甚至比单个庞大查询更能提高性能。
• 数据暂存：在从外部源或复杂联接获取数据之后，在清理、转换并将其插入到最终永久表之前，对其进行保存。
• 隔离操作：在将最终结果应用到永久表之前，在临时表中对数据子集执行复杂的计算或更新。

2. 创建和删除临时表（跨平台）

创建临时表的语法在大多数 RDBMS 中相似，但略有不同。

MySQL 和 PostgreSQL

两者都使用 CREATE TEMPORARY TABLE 语法。当会话结束时，表会自动删除。

-- 创建一个临时表
CREATE TEMPORARY TABLE Temp_ActiveUsers (
    user_id INT PRIMARY KEY,
    last_login_date DATE
);

-- 手动删除临时表（可选，因为它在会话结束时会自动删除）
DROP TEMPORARY TABLE Temp_ActiveUsers;

PostgreSQL 提供了一个 ON COMMIT 子句（CREATE TEMP TABLE … ON COMMIT [PRESERVE ROWS | DELETE ROWS | DROP]），它允许您对事务中表的行为进行精细控制。

SQL Server (T-SQL)

SQL Server 使用 # 前缀表示本地临时表，## 表示全局临时表。

-- 创建一个本地临时表（仅当前会话可见）
CREATE TABLE #Temp_ProductSales (
    product_id INT,
    total_sales DECIMAL(12, 2)
);

-- 创建一个全局临时表（所有会话可见）
-- 由于可能存在命名冲突，请谨慎使用。
CREATE TABLE ##Shared_Config (
    config_key VARCHAR(50),
    config_value VARCHAR(255)
);

-- 手动删除表
DROP TABLE #Temp_ProductSales;

3. 临时表的范围和生命周期

• 会话作用域 (MySQL, PostgreSQL, SQL Server 本地 #)：表在创建它的数据库会话中创建并仅在此会话中可见。其他会话无法看到或访问它。当会话关闭时，它会自动销毁。
• 全局作用域 (SQL Server 全局 ##)：该表对所有活动的数据库会话可见。当创建它的会话关闭且没有其他会话正在主动使用它时，它会自动销毁。

4. 实际用例：为报告暂存数据

想象一下，您需要生成上个月消费最高的客户报告。计算涉及联接订单和订单项。临时表可以简化此过程。

步骤 1：创建一个临时表，用于保存每位客户的月度消费。

CREATE TEMPORARY TABLE CustomerSpending (
    customer_id INT PRIMARY KEY,
    total_spent DECIMAL(10, 2) NOT NULL
);

步骤 2：使用聚合数据填充临时表。

INSERT INTO CustomerSpending (customer_id, total_spent)
SELECT
    o.customer_id,
    SUM(oi.quantity * oi.unit_price)
FROM Orders o
JOIN OrderItems oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2023-10-01' AND o.order_date < '2023-11-01'
GROUP BY o.customer_id;

步骤 3：通过与 Customers 表联接生成最终报告。

SELECT
    c.name,
    c.email,
    cs.total_spent
FROM Customers c
JOIN CustomerSpending cs ON c.customer_id = cs.customer_id
ORDER BY cs.total_spent DESC
LIMIT 10;

5. 现代替代方案：CTE 和表变量

通用表表达式 (CTEs)

为了简化单个复杂查询，CTE（使用 WITH 子句）通常是更好的选择。CTE 仅在一个语句的持续时间内存在。它提高了可读性，并且没有在临时存储中创建物理表的开销。

WITH CustomerSpending AS (
    SELECT
        o.customer_id,
        SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM Orders o
    JOIN OrderItems oi ON o.order_id = oi.order_id
    WHERE o.order_date >= '2023-10-01' AND o.order_date < '2023-11-01'
    GROUP BY o.customer_id
)
SELECT
    c.name,
    c.email,
    cs.total_spent
FROM Customers c
JOIN CustomerSpending cs ON c.customer_id = cs.customer_id
ORDER BY cs.total_spent DESC
LIMIT 10;

表变量 (SQL Server)

SQL Server 还提供了表变量（例如，DECLARE @MyTable TABLE (…)）。它们类似于本地临时表，但作用域仅限于当前批处理或存储过程，并且通常涉及较少的日志记录，这使得它们对于非常小的数据集来说更轻量。然而，它们缺乏统计信息，这可能导致处理大量数据时性能不佳。