临时表
# 临时表
# 临时表的特性
- 临时表利用了数据库临时表空间，由数据库系统自动进行维护，因此节省了物理表空间。并且由于临时表空间一般利用虚拟内存，大大减少了硬盘的 I/O 次数，因此也提高了系统效率。
- 临时表只在创建它的会话（session）生命周期内存在。
- 当创建该临时表的会话结束时（比如客户端断开连接或事务结束），临时表会被自动删除，不必记得用完后删除数据。
- 对其他会话是不可见的，保证了数据的隔离性。
sql server 有 本地临时表 和 全局临时表 两个概念
# 应用场景
临时表可以用于多种场景，如存储查询结果、过滤数据、处理大量数据等。在实际应用中，临时表可以显著提高查询效率，因为它避免了每次查询都从原始表中提取数据的需要。
# 基础操作
# 创建临时表
写法一
要创建一个临时表，可以使用 CREATE TEMPORARY TABLE 语句。例如：
CREATE TEMPORARY TABLE temp_table_name (
column1 datatype,
column2 datatype,
...
);
1
2
3
4
5
2
3
4
5
这里的 temporary_table 是临时表的名称，后面的括号内列出了表中的列及其数据类型。
写法二
可以简写为：
CREATE TEMPORARY TABLE temp_table_name AS
SELECT column1, column2, ...
FROM source_table
WHERE condition;
1
2
3
4
2
3
4
写法三
使用 WITH 子句（适用于 PostgreSQL, SQL Server, MySQL 8.0+）
WITH 子句（公共表表达式 CTE）允许你将参数转换为一个虚拟的临时表，可以直接在查询中使用。
WITH temp_table AS (
SELECT 1 AS id
UNION ALL
SELECT 2
UNION ALL
SELECT 3
-- 添加更多的值
UNION ALL
SELECT n
)
SELECT * FROM temp_table;
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
解释：
WITH子句创建了一个临时表temp_table，它包含了你传入的所有参数。UNION ALL用于将多个SELECT语句的结果合并成一个表格。
写法四
对于较小的参数集，你可以直接使用 VALUES 进行查询，而不创建临时表：
SELECT *
FROM (VALUES (1), (2), (3), ..., (n)) AS temp_table(id)
WHERE temp_table.id IN (SELECT id FROM user);
-- 使用示例
SELECT *
FROM user u
WHERE EXISTS (
SELECT 1
FROM (VALUES (1), (2), (3), ..., (n)) AS temp_table(id)
WHERE temp_table.id = u.id
);
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 插入数据到临时表
INSERT INTO temp_table_name (column1, column2, ...)
VALUES (value1, value2, ...);
1
2
2
# 查询临时表
SELECT * FROM temp_table_name;
1
# 修改临时表
临时表的修改操作与普通表类似，可以使用 ALTER TABLE 命令。
ALTER TABLE temp_table_name
ADD COLUMN new_column datatype;
1
2
2
# 删除临时表
虽然临时表会在会话结束时自动删除，但有时候可能需要手动删除它们。这可以通过 DROP TEMPORARY TABLE 命令来实现。例如：
DROP TEMPORARY TABLE temporary_table;
1
这个命令会立即删除指定的临时表，并释放其占用的空间。
# 具体示例
-- 创建临时表
CREATE TEMPORARY TABLE temp_orders AS
SELECT * FROM orders WHERE order_date >= '2023-01-01';
-- 查询临时表
SELECT * FROM temp_orders;
-- 插入数据到临时表
INSERT INTO temp_orders (order_id, customer_id, order_date)
VALUES (1001, 1, '2023-01-05');
-- 查询临时表
SELECT * FROM temp_orders;
-- 删除临时表
DROP TEMPORARY TABLE IF EXISTS temp_orders;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 注意事项
- 版本兼容性：MySQL 的临时表功能从 3.23 版本开始支持。如果使用的 MySQL 版本低于 3.23，则无法使用临时表。
- 性能影响：虽然临时表可以提高查询效率，但它们也会增加内存的使用，因此在处理大型数据集时应谨慎使用。
- 安全性：由于临时表在会话结束时自动删除，因此不需要担心数据泄露的问题。但是，为了防止意外的数据丢失，建议在创建临时表之前进行充分的测试。
# 学习参考
上次更新: 2025/1/6 17:59:10