MySQL：临时表学习

前言

在 MySQL 中，临时表（Temporary Table）是一种非常有用的工具，可以帮助我们在执行复杂查询时存储临时数据。
临时表的存在时间仅限于会话期，当会话结束后，临时表自动销毁。【数据库会话指的是用户连接到数据库并执行命令的整个时间段。一个会话从用户连接到数据库开始，直到用户断开连接或者显式结束会话（例如通过退出或关闭连接）为止。】

什么是临时表

• 临时表是一种只在当前会话（session）有效的表，一旦会话结束，MySQL 会自动删除这些临时表。
• 临时表用于在复杂查询中存储中间结果，或者用于需要临时存储数据进行后续处理的场景。

创建临时表

MySQL 提供 CREATE TEMPORARY TABLE 语法来创建临时表。创建语法与普通表相同，但需要在 CREATE TABLE 前加上 TEMPORARY 关键字。
语法：

CREATE TEMPORARY TABLE 表名 (
  列1 数据类型 [约束],
  列2 数据类型 [约束],
  ...
);

示例：

-- 创建一个临时表，用于存储用户的临时信息
CREATE TEMPORARY TABLE temp_users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);

-- 向临时表插入数据
INSERT INTO temp_users (id, name, email) 
VALUES (1, 'Alice', '[email protected]'),
       (2, 'Bob', '[email protected]');

-- 查询临时表中的数据
SELECT * FROM temp_users;

注意：临时表只能在创建它的会话中访问，其他会话无法访问该表。（例如：使用DBeaver测试，新建SQL编辑器A，创建了临时表，然后再新建SQL编辑器B，此时在编辑器B中是无法使用临时表的，查询会提示表不存在）

临时表的特点

• 生命周期：临时表只在会话期间存在，一旦会话结束，MySQL 会自动删除临时表。即使在会话期间发生错误，临时表也不会被删除，除非明确使用 DROP TABLE 命令。
• 隔离性：不同会话中的临时表互不干扰，即使不同的会话创建了同名的临时表，它们之间也不会冲突。
• 结构和数据隔离：即使同名的永久表存在，临时表的结构和数据也不会影响或被影响。

示例：

-- 创建永久表和临时表
CREATE TABLE users (id INT, name VARCHAR(50));
CREATE TEMPORARY TABLE users (id INT, name VARCHAR(50), age INT);

-- 向临时表插入数据
-- 当在 MySQL 中有一个永久表（Permanent Table）和一个同名的临时表（Temporary Table）存在时，在向表中插入数据时，会插入到临时表中。
-- 原因：MySQL 在执行 SQL 操作时会优先选择临时表，因为临时表的优先级高于永久表。这意味着如果存在同名的永久表和临时表，对该表的数据操作（包括插入、查询、更新等）会影响到临时表，而不是永久表。这种优先级的设定是为了确保临时表的使用不会受到永久表的影响，以保证临时表在会话结束时可以被正确清理和释放，不会影响到永久数据的完整性和稳定性。
INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25);

-- 此处查询的为临时表中的数据
SELECT * FROM users;

-- 删除临时表后，查询永久表
DROP TEMPORARY TABLE users;
SELECT * FROM users;

临时表的使用场景

存储复杂查询的中间结果

在一些复杂的查询中，某些中间结果可能会被多次使用。通过临时表存储这些中间结果，避免多次重复计算，提升查询性能。

数据处理中的临时存储

在数据批处理或者多步处理时，临时表可以用来存储临时数据。比如，ETL（Extract, Transform, Load）流程中，通常会使用临时表来存储清洗后的数据。

避免全表扫描

在需要处理大量数据时，通过临时表分阶段处理，可以避免对大表进行全表扫描，提高处理速度。

示例：

-- 步骤1：创建临时表存储复杂查询的中间结果
CREATE TEMPORARY TABLE temp_sales AS
SELECT product_id, SUM(sales) AS total_sales
FROM orders
GROUP BY product_id;

-- 步骤2：基于临时表进行进一步处理
SELECT ts.product_id, ts.total_sales, p.product_name
FROM temp_sales ts
JOIN products p ON ts.product_id = p.id
WHERE ts.total_sales > 1000;

临时表的性能优化

虽然临时表对开发非常有用，但在某些情况下可能会引发性能问题，特别是涉及大数据量时。以下是一些优化建议：

• 避免频繁创建和删除临时表
  创建和删除临时表是有一定开销的，特别是表结构较复杂时。如果临时表反复创建删除，可以考虑使用持久化表或内存表。
• 适当的索引
  如果临时表存储了大量数据，且需要对某些字段进行频繁查询或连接操作，考虑在这些字段上创建索引。
• 合理使用内存表
  在小数据量场景下，可以将临时表存储在内存中，而非磁盘上。MySQL 的 MEMORY 存储引擎支持在内存中创建表，避免 I/O 开销。

常见问题

临时表与持久化表同名冲突

如果一个持久化表和一个临时表同名，查询时优先使用临时表。为避免冲突，建议使用不同的表名或显式地删除临时表。

内存占用问题

如果临时表存储大量数据，可能会导致内存占用过高，影响其他操作的执行效率。因此在存储大数据量时，尽量使用索引，并定期清理不必要的数据。

临时表未能自动删除

虽然 MySQL 会在会话结束后自动删除临时表，但如果连接没有正确关闭（例如异常终止），临时表可能无法自动清理。需要确保数据库连接和会话正确管理。

结语

MySQL 的临时表为处理复杂查询和存储临时数据提供了很好的解决方案。在使用时，了解其生命周期、特点和使用场景，并结合性能优化策略，可以更好地发挥其作用。希望本文的介绍能够帮助你在实际开发中更好地利用 MySQL 临时表。