MySQL 临时表与临时视图操作指南

在日常的 SQL 查询与数据处理过程中，我们经常需要构建一些临时的数据集合来进行关联（JOIN）或进一步的查询。根据需求的不同，主要有以下几种实现方式。

一、 在单条查询中创建“临时视图”或注入临时数据

这种方式不会在数据库中真正创建实体表，数据仅在当前这一条 SQL 语句执行期间存在，非常适合用于字典映射、临时过滤或构建虚拟数据表。推荐使用 CTE（公用表表达式，WITH 语句） 来实现。

1. 使用 VALUES ROW()（MySQL 8.0.19 及以上推荐）

这是最优雅的写法，可以像写 INSERT 语句一样直接在查询中构造出一个完整的临时表数据。

-- 使用 WITH 定义临时表的内容和列名 (id, name, department)
WITH my_temp_table AS (
    SELECT * FROM (
        VALUES 
            ROW(1, '张三', '技术部'),
            ROW(2, '李四', '市场部'),
            ROW(3, '王五', '财务部'),
            ROW(4, '赵六', '技术部')
    ) AS t(id, name, department) -- 此处指定列名
)

-- 像查一张真正的表一样查询它，或者与其它表 JOIN
SELECT * 
FROM my_temp_table 
WHERE department = '技术部';

2. 使用 UNION ALL（MySQL 5.7 及所有版本通用）

如果使用的 MySQL 版本较老，不支持 VALUES 语法，可以通过 UNION ALL 将多行数据拼接成一个临时结果集。第一行用于定义列名。

WITH my_temp_table AS (
    -- 第一行定义列名和第一条数据
    SELECT 1 AS id, '张三' AS name, '技术部' AS department
    UNION ALL
    -- 后续行直接写入对应的数据即可
    SELECT 2, '李四', '市场部'
    UNION ALL
    SELECT 3, '王五', '财务部'
    UNION ALL
    SELECT 4, '赵六', '技术部'
)

-- 在主查询中使用
SELECT name, department 
FROM my_temp_table 
ORDER BY id DESC;

3. 从已有表中提取临时视图（最常见的 CTE 用法）

如果你不是想凭空捏造数据，而是想把复杂的子查询抽离出来作为一个临时视图，也可以使用 WITH。

WITH TempHighValueUsers AS (
    SELECT user_id, username, total_spent
    FROM users
    WHERE total_spent > 1000
)
-- 在主查询中直接 JOIN 这个临时结果集
SELECT t.username, o.order_date, o.amount
FROM TempHighValueUsers t
JOIN orders o ON t.user_id = o.user_id;

---

二、 创建真正的临时表（TEMPORARY TABLE）

如果你需要的是先建一个表，分步骤插入大量数据，然后再进行复杂的查询或者多次使用，那么应该使用 MySQL 的 临时表（TEMPORARY TABLE）。

临时表的特点：

• 隔离性：只在当前数据库连接（会话）中可见，不同连接中即使临时表同名也不会互相冲突。
• 生命周期：当前会话关闭或断开连接时，临时表及其数据会自动被销毁，不会在数据库留下垃圾文件。

操作步骤示例：

-- 1. 创建临时表
CREATE TEMPORARY TABLE temp_users (
    id INT,
    name VARCHAR(50),
    age INT
);

-- 2. 插入完整的表内容
INSERT INTO temp_users (id, name, age) VALUES 
(1, '张三', 25),
(2, '李四', 30),
(3, '王五', 28);

-- 3. 进行 SELECT 查询或 JOIN
SELECT * FROM temp_users WHERE age > 26;

-- 注意：你可以随时使用 DROP TEMPORARY TABLE temp_users; 手动删除，
-- 也可以直接关闭连接，数据库会自动回收。