MySQL 临时表：工作原理、优势与最佳实践

1. Introduction

在使用 MySQL 时，“临时表”是一种用于临时存储和处理数据的有用方式。通过使用临时表，你可以临时保存数据，以降低复杂查询的负载并提升批处理的效率。

本文将详细说明 MySQL 临时表是什么，以及它们的使用场景和优势。

What Is a Temporary Table?

临时表是 仅在会话内有效的表。
与常规表不同，临时表不会永久存储在数据库中，并且 会在会话结束时自动删除。

临时表的关键特性如下：

• 它们按会话存在（其他连接不可访问）
• 会在会话结束时自动删除
• 即使存在同名的常规表，也可以使用而不产生冲突
• 常用于提升性能

临时表非常适合数据分析和临时数据处理，且常被用作 批处理和聚合任务的支持。

Benefits of Using Temporary Tables

使用临时表可以使数据处理更高效。以下是三个主要好处。

1. Improve query performance

在处理大量数据时，使用多个 JOIN 和子查询会使处理变得复杂并增加数据库负载。使用临时表，你可以 提前过滤并存储数据，从而加快查询执行速度。

2. Ideal for temporary data storage

在批处理或数据转换过程中，你可能需要临时存储数据并执行必要的操作。临时表允许你临时保存数据，并实现 快速的内存处理。

3. Keep existing data safe

直接操作生产数据风险很大。通过使用临时表，你可以 在不更改生产数据的情况下处理数据，降低错误风险。

Summary

MySQL 临时表是用于临时数据存储和处理的便利工具。

• 它们是会话作用域的，会在会话结束时被删除
• 它们有助于提升性能和进行批处理
• 它们允许在不更改生产数据的情况下安全操作

2. Temporary Table Basics

MySQL 临时表用于临时存储数据，与常规表不同。本节将详细解释临时表的基本概念，包括“与常规表的区别”和“与内部临时表的区别”。

Differences Between Temporary Tables and Regular Tables

临时表和常规表在 数据保留和访问行为 上有显著差异。下表概括了主要区别。

Key points

• 临时表是 按会话隔离 的，其他用户不可见。
• 即使同名的常规表已存在，也可以无错误地创建临时表 。
• 它们通过 CREATE TEMPORARY TABLE 明确创建，并且 会在会话结束时自动删除 。

Differences Between Temporary Tables and Internal Temporary Tables

除了用户创建的 临时表，MySQL 还会自动创建 内部临时表。它们听起来相似，但用途和管理方式不同。

What is an internal temporary table?

• MySQL 可能 在内部创建临时表 来优化某些查询（例如 GROUP BY、ORDER BY、DISTINCT）。
• 最终用户无法直接管理它们（不能像 CREATE TEMPORARY TABLE 那样显式创建）。
• 它们在查询执行期间按需创建，并且 在查询完成后自动删除 。

Example that may trigger internal temporary tables

当你运行如下查询时，MySQL 可能会创建内部临时表来处理它。

SELECT category, COUNT(*) 
FROM products 
GROUP BY category
ORDER BY COUNT(*) DESC;

在这种情况下，MySQL 可能会创建一个 内部临时表来临时存储 GROUP BY 结果，
随后使用它来计算最终输出。

摘要

• 临时表是用户创建的临时表，在会话结束时会自动删除。
• 不同于常规表，它不能被其他会话访问。
• 内部临时表由 MySQL 自动创建和删除，用户无法直接控制它。

3. 如何创建临时表

您可以使用 CREATE TEMPORARY TABLE 语句创建 MySQL 临时表。在本节中，我们将从基础创建到基于已有表创建的所有内容进行说明。

创建临时表的基本方法

在 MySQL 中，使用 CREATE TEMPORARY TABLE 来创建临时表。

基本语法

CREATE TEMPORARY TABLE table_name (
    column_name data_type constraints,
    column_name data_type constraints,
    ...
);

示例代码

以下 SQL 创建了一个名为 users_temp 的临时表，包含三列：id（整数）、name（字符串）和 email（字符串）。

CREATE TEMPORARY TABLE users_temp (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

由于该表在会话结束时会自动删除，它不会影响持久化数据库。

基于已有表创建临时表

除了从头创建临时表外，您还可以 复制已有表的结构 来创建临时表。

使用 CREATE TEMPORARY TABLE ... SELECT

在 MySQL 中，您可以基于 SELECT 语句的结果创建临时表。

基本语法

CREATE TEMPORARY TABLE temp_table_name
SELECT * FROM existing_table_name;

示例代码

例如，要复制 users 表的数据结构并创建一个新的临时表 users_temp，可以这样写：

CREATE TEMPORARY TABLE users_temp
SELECT * FROM users;

使用此方法，users 的列结构会被复制到 users_temp，但 如 PRIMARY KEY 和 INDEX 等约束不会被复制。

如果只想复制表结构而不包括数据，可添加 WHERE 1=0。

CREATE TEMPORARY TABLE users_temp
SELECT * FROM users WHERE 1=0;

使用此 SQL，users 的列定义会被复制，但不包含任何数据。

创建临时表时的注意事项

1. 临时表是会话范围的

• 临时表仅在创建它的会话中有效。
• 其他连接或用户无法访问它。

2. 即使存在同名的常规表也可以创建临时表

• 例如，即使数据库中已有名为 users 的常规表，也可以创建同名的临时表 users。
• 在该会话中，临时表具有优先权，常规表会被隐藏。

3. 存储引擎的影响

• 默认情况下，临时表使用 MEMORY 引擎，但如果数据量较大，可能会存储在 InnoDB 临时区。
• 若想显式指定 MEMORY 引擎，可这样写：CREATE TEMPORARY TABLE users_temp ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) ) ENGINE=MEMORY;
• MEMORY 引擎速度快，但有数据大小限制。对于大数据集，建议使用 InnoDB 。

小结

• 使用 CREATE TEMPORARY TABLE 创建临时表。
• 也可以通过复制已有表（SELECT * FROM）来创建临时表。
• MEMORY 引擎速度快，但对于大数据集，InnoDB 通常更合适。
• 临时表按会话管理，会在会话结束时自动删除。

4. 如何使用临时表

MySQL 临时表可以像普通表一样操作，包括 INSERT、UPDATE、DELETE 和 SELECT。本节将详细解释每种操作。

插入数据

要向临时表添加数据，请使用常规的 INSERT INTO 语句。

基本语法

INSERT INTO temp_table_name (column1, column2, ...)
VALUES (value1, value2, ...);

示例代码

以下 SQL 将数据插入名为 users_temp 的临时表。

INSERT INTO users_temp (id, name, email)
VALUES (1, 'Taro Yamada', 'taro@example.com');

您也可以从已有表复制并插入数据。

INSERT INTO users_temp (id, name, email)
SELECT id, name, email FROM users WHERE age >= 18;

此 SQL 将 users 表中 年龄在 18 岁及以上的用户 数据插入临时表。

更新数据

要修改临时表中的数据，请使用常规的 UPDATE 语句。

基本语法

UPDATE temp_table_name
SET column_name = new_value
WHERE condition;

示例代码

例如，要更改 users_temp 表中 id=1 的用户的姓名：

UPDATE users_temp
SET name = 'Ichiro Sato'
WHERE id = 1;

删除数据

要删除不需要的数据，请使用 DELETE 语句。

基本语法

DELETE FROM temp_table_name WHERE condition;

示例代码

例如，要从 users_temp 中删除 id=1 的行：

DELETE FROM users_temp WHERE id = 1;

要删除表中 所有数据，请省略 WHERE 子句。

DELETE FROM users_temp;

请注意，使用 DELETE 并不会删除表本身，只会删除其中的数据。

查询数据

要检索临时表中存储的数据，请使用 SELECT 语句。

基本语法

SELECT column_name FROM temp_table_name WHERE condition;

示例代码

例如，要检索 users_temp 中的所有数据：

SELECT * FROM users_temp;

要检索符合特定条件的数据，请使用 WHERE 子句。

SELECT * FROM users_temp WHERE email LIKE '%@example.com';

此 SQL 仅检索电子邮件地址包含 @example.com 的行。

使用临时表时的注意事项

1. 会话结束时数据会被移除

• 临时表按 会话 管理，会话结束时其数据也会被移除。
• 对于长时间运行的处理，建议定期备份数据。

2. 使用相同名称创建临时表会导致错误

• 如果使用 CREATE TEMPORARY TABLE 创建同名临时表，会产生错误。
• 作为 避免错误的做法，请事先执行 DROP TEMPORARY TABLE IF EXISTS。DROP TEMPORARY TABLE IF EXISTS users_temp; CREATE TEMPORARY TABLE users_temp (...);

3. 存储引擎限制

• 临时表默认使用 MEMORY 引擎，但对于大型数据集，可能会自动存储在 InnoDB 临时区。
• 对于大型数据集，建议使用 InnoDB 临时表。

小结

• 临时表可以像普通表一样执行 INSERT、UPDATE、DELETE 和 SELECT。
• 当会话结束时，临时表中的数据也会自动被移除。
• 事先运行 DROP TEMPORARY TABLE IF EXISTS 有助于避免名称冲突错误。
• 对于大型数据集，建议使用 InnoDB 临时表。

5. 管理和删除临时表

MySQL 临时表会在会话结束时自动删除。但在某些情况下，您可能需要显式删除它们。本节解释如何管理和删除临时表。

如何删除临时表

要显式删除临时表，请使用 DROP TEMPORARY TABLE 语句。

基本语法

DROP TEMPORARY TABLE table_name;

示例代码

例如，要删除名为 users_temp 的临时表，请运行：

DROP TEMPORARY TABLE users_temp;

运行此 SQL 后，users_temp 表将被删除，并且在会话中无法再使用。

会话结束时的自动删除

临时表在会话结束时会自动删除。

自动删除的工作原理

1. 使用 CREATE TEMPORARY TABLE 创建临时表
2. 在会话处于活动状态时操作其数据
3. 当会话（连接）关闭时，临时表会自动删除

但是，在以下情况下要小心：

• 会话保持打开较长时间 → 不必要的临时表可能会消耗内存，因此建议根据需要运行 DROP TEMPORARY TABLE。
• 处理大量数据时 → 为避免存储压力，适当删除表非常重要。

使用 DROP TEMPORARY TABLE IF EXISTS

为了避免删除可能不存在的表时出错，您可以使用 IF EXISTS。

基本语法

DROP TEMPORARY TABLE IF EXISTS table_name;

示例代码

DROP TEMPORARY TABLE IF EXISTS users_temp;

此 SQL 如果 users_temp 存在则删除它；如果不存在，则不会引发错误。

常见错误及修复

错误 1： “表未找到”

发生时机：

• 使用 DROP TEMPORARY TABLE 尝试删除不存在的表时
• 因为临时表是会话范围的，您无法从其他会话中删除它们

修复方法：

• 添加 IF EXISTS 以避免错误

  DROP TEMPORARY TABLE IF EXISTS users_temp;
  

• 在正确的会话中删除它

错误 2： “表已存在”

发生时机：

• 尝试使用已存在的名称创建临时表时

修复方法：

• 事先运行 DROP TEMPORARY TABLE IF EXISTS

  DROP TEMPORARY TABLE IF EXISTS users_temp;
  CREATE TEMPORARY TABLE users_temp (
      id INT PRIMARY KEY,
      name VARCHAR(50),
      email VARCHAR(100)
  );
  

管理临时表的最佳实践

1. 在不再需要时显式删除它们

• 根据需要运行 DROP TEMPORARY TABLE 以释放不必要的表。

1. 使用 IF EXISTS 避免错误

• DROP TEMPORARY TABLE IF EXISTS 可防止删除不存在的表时出错。

1. 注意会话管理

• 长时间运行的会话可能会导致临时表消耗内存，因此要适当删除它们。

1. 了解存储引擎的影响

• MEMORY 引擎速度快，但有数据大小限制。
• 如果使用 InnoDB，则需要考虑磁盘空间使用。

总结

• 您可以使用 DROP TEMPORARY TABLE 显式删除临时表。
• 它们在会话结束时会自动删除，但对于长时间运行的会话，建议手动清理。
• DROP TEMPORARY TABLE IF EXISTS 有助于防止删除时出错。
• 了解如何处理“表未找到”和“表已存在”错误很有用。

6. 临时表的实际用例

MySQL 临时表用于使临时数据存储和处理更高效。在本节中，我们介绍临时表有用的常见场景，并解释实现细节。

1. 用作聚合的中间表

在数据分析和报告生成中，直接处理大型数据集可能会减慢查询执行。通过使用临时表，您可以先组织数据，然后再处理它，从而提高性能。

场景

• sales 表包含一年的销售数据。
• 您想要计算每月总销售额并进行进一步分析。

示例实现

CREATE TEMPORARY TABLE monthly_sales (
    month_year DATE,
    total_sales DECIMAL(10,2)
);

INSERT INTO monthly_sales (month_year, total_sales)
SELECT DATE_FORMAT(sale_date, '%Y-%m-01') AS month_year, SUM(amount) 
FROM sales
GROUP BY month_year;

SELECT * FROM monthly_sales;

2. Keeping temporary data for batch processing

Temporary tables are also useful for batch processing (bulk operations). For example, you can filter data by certain conditions and store only the target data in a temporary table to operate efficiently.

Scenario

• From the users table, you want to email only users who have logged in within the last year .
• You store the target data in a temporary table first, then process it sequentially.

Example implementation

CREATE TEMPORARY TABLE active_users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

INSERT INTO active_users
SELECT id, name, email FROM users WHERE last_login >= NOW() - INTERVAL 1 YEAR;

SELECT * FROM active_users;

3. Simplifying complex queries

Running complex queries directly can reduce performance and hurt readability. By using temporary tables, you can reduce subqueries and keep SQL simpler.

Scenario

• You want to get the top 10 best-selling products from the orders table.
• You want to avoid using subqueries by leveraging a temporary table.

Example implementation

CREATE TEMPORARY TABLE top_products AS
SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 10;

SELECT * FROM top_products;

4. Temporary operations without needing rollback

Temporary tables are managed per session and are not affected by transactions. This makes them suitable for managing temporary data where rollback is not desired.

Scenario

• During a transaction, you want to keep temporary calculation results .
• But you want to avoid temporary data being rolled back on errors.

Example implementation

START TRANSACTION;

CREATE TEMPORARY TABLE temp_results (
    user_id INT,
    score INT
);

INSERT INTO temp_results
SELECT user_id, SUM(points) FROM game_scores GROUP BY user_id;

-- Commit the transaction
COMMIT;

SELECT * FROM temp_results;

Summary

• Temporary tables can be used for aggregation, batch processing, and simplifying queries across many scenarios.
• Using them as an intermediate table can improve performance and help organize data.
• For batch processing , pre-extracting only target data helps avoid unnecessary work.
• For simplifying complex queries , reducing subqueries improves readability.
• Because they are not affected by transactions , they can be used for temporary data where rollback is not needed.

7. Alternatives and Limitations of Temporary Tables

MySQL temporary tables are useful, but they have some limitations. In some cases, using alternatives such as views or subqueries can provide more efficient data processing. In this section, we explain the main limitations of temporary tables and alternative approaches to work around them.

Main limitations of temporary tables

Temporary tables have several limitations that regular tables do not. Understanding these helps you choose appropriate use cases.

1. Session-scoped

• A temporary table is valid only within the session where it was created, and cannot be accessed by other connections or users .
• Even if a regular table with the same name exists, the temporary table takes precedence within the session (the regular table is not accessible).

2. The schema is not retained

• Regular tables can be inspected with SHOW CREATE TABLE , but a temporary table disappears when the session ends , so its schema is not retained.

3. Index limitations

• 如果在 CREATE TEMPORARY TABLE 中未指定 PRIMARY KEY 或 INDEX，它们不会自动创建。
• 如果需要在临时表上创建索引，必须手动创建。

4. 默认存储引擎是 MEMORY

• 使用 MEMORY 引擎时，大数据量可能导致交换到磁盘并降低性能。
• 如果指定 InnoDB，它可以处理更大的数据，但磁盘使用量会增加。

5. 不受事务影响

• 临时表 不受 ROLLBACK 影响。
• 因此，它们不适合需要严格事务一致性的处理。

临时表的替代方案

为避免这些限制，您可以使用 视图或子查询 代替临时表，以实现更灵活的数据处理。

1. 使用视图

视图可以像临时表一样用于临时数据引用。视图充当 虚拟表，不需要临时数据存储，这有助于避免存储限制。

创建视图

CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE last_login >= NOW() - INTERVAL 1 YEAR;

使用视图

SELECT * FROM active_users;

使用视图的优点

✅ 无存储使用（数据直接引用，无需临时存储）
✅ 不依赖会话（可供其他用户和连接使用）
✅ 可以保留模式（可使用 SHOW CREATE VIEW 查看定义）

使用视图的缺点

❌ 难以更新（对视图的直接 INSERT 或 UPDATE 受限）
❌ 大数据集时性能可能下降

2. 使用子查询

您也可以使用 子查询 来处理临时数据，而无需创建临时表。

使用临时表

CREATE TEMPORARY TABLE top_products AS
SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 10;

SELECT * FROM top_products;

使用子查询

SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 10;

使用子查询的优点

✅ 因为不创建临时表而获得更好的性能
✅ 无存储使用
✅ 不依赖会话，可随时执行

使用子查询的缺点

❌ 复杂查询时可读性可能下降
❌ 难以复用数据（可能需要重复引用相同数据）

3. 使用 CTE（WITH 子句）

在 MySQL 8.0 及以上版本中，您可以使用 CTE（公共表表达式） 来临时处理数据，而无需创建临时表。

CTE 示例

WITH top_products AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY product_id
    ORDER BY total_sales DESC
    LIMIT 10
)
SELECT * FROM top_products;

使用 CTE 的优点

✅ 提升可读性（通常比子查询更易读）
✅ 可优化性能（无需创建临时表的临时式处理）

使用 CTE 的缺点

❌ 在 MySQL 5.x 中不可用（仅在 MySQL 8.0 及以上版本支持）

总结

8. 常见问题

以下是关于 MySQL 临时表的常见问题。希望能帮助您了解它们的行为及其限制。

1. 临时表可以被其他会话引用吗？

不可以。
临时表 仅在创建它的会话内可用。其他会话无法访问它。即使其他用户创建了同名的临时表，每个会话也会将其视为独立的表。

2. 创建临时表需要哪些权限？

要创建临时表，您需要在数据库上拥有 CREATE TEMPORARY TABLES 权限。
要将此权限授予用户，请运行以下 SQL：

GRANT CREATE TEMPORARY TABLES ON database_name.* TO 'user_name'@'host';

您也可以使用 SHOW GRANTS 来检查当前的权限。

SHOW GRANTS FOR 'user_name'@'host';

3. 临时表会影响磁盘使用吗？

会的。
默认情况下，MySQL 临时表使用 MEMORY 引擎，但 当数据大小超过一定阈值时，它们会存储在 InnoDB 临时区。

在处理大数据集时，临时表可能会占用磁盘空间。因此，建议在不再需要时显式删除它们。

DROP TEMPORARY TABLE IF EXISTS table_name;

为了将磁盘影响降到最低，如果您预计会有大量数据，建议使用 InnoDB 而不是 MEMORY 来创建临时表。

CREATE TEMPORARY TABLE table_name (
    column1 data_type,
    column2 data_type
) ENGINE=InnoDB;

4. 临时表和内部临时表有什么区别？

5. 临时表可以在线程之间共享吗？

不能。
临时表 仅在创建它的线程（会话）内有效，其他线程或进程无法访问它。

如果需要在会话/线程之间共享数据，必须创建普通表。

CREATE TABLE shared_temp_table (
    id INT PRIMARY KEY,
    data VARCHAR(255)
);

6. 临时表会降低性能吗？

在某些情况下会。
尤其需要注意以下情形：

• 数据量过大时
• MEMORY 引擎有大小限制；超出后，数据可能会切换到 InnoDB，从而 降低性能。
• 缓解措施： 如果预计会超出 MEMORY 限制，请从一开始就使用 InnoDB 创建表。
• 未设置合适的索引时
• 使用 CREATE TEMPORARY TABLE ... SELECT 创建的表 不会复制索引，导致查询变慢。
• 缓解措施： 使用 ALTER TABLE 按需添加索引。

  ALTER TABLE temp_table_name ADD INDEX (column_name);
  

7. 如何提升临时表的性能？

提升临时表性能的有效方法包括：

✅ 使用 MEMORY 引擎（对小数据集速度快）

CREATE TEMPORARY TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=MEMORY;

✅ 只选择必要的列（省去不需要的列）

CREATE TEMPORARY TABLE users_temp AS
SELECT id, name FROM users;

✅ 添加合适的索引（加速查询）

ALTER TABLE users_temp ADD INDEX (name);

✅ 在不再需要时立即删除（释放内存）

DROP TEMPORARY TABLE IF EXISTS users_temp;

Summary

• 临时表不能被其他会话或线程引用
• 创建临时表需要 CREATE TEMPORARY TABLES 权限
• 如果数据过大，MySQL 可能会从 MEMORY 切换到 InnoDB，这会降低性能
• 添加适当的索引可以加快查询速度
• 当临时表不再需要时，建议使用 DROP TEMPORARY TABLE 将其删除

以上即为 MySQL 临时表的详细说明，涵盖了基本概念、使用场景、限制、替代方案以及常见问答。
合理使用临时表可以显著提升数据处理的效率。