MySQL - 临时表

MySQL：掌握临时表（Temporary Tables）

临时表（Temporary tables）是 MySQL 中一种特殊类型的表，它允许您在单个数据库会话（database session）中存储一个中间结果集（intermediate result set），并对其进行使用和操作。它们对于复杂的报表（reporting）、分解大型计算以及管理会话专用数据（session-specific data）而又不混乱主数据库结构（main database schema）来说，都非常有用。

什么是临时表？

临时表，顾名思义，只临时存在。其主要特点是：

• 会话范围（Session-Scoped）： 它仅对创建它的客户端会话可见。其他数据库连接无法看到或访问它。
• 自动删除（Automatic Deletion）： 当客户端会话结束时（例如，当您断开连接或脚本执行完毕时），它会自动被删除。
• 名称遮蔽（Name Shadowing）： 您可以创建一个与永久表同名的临时表。在这种情况下，在会话期间，所有查询都将引用临时表。永久表不受影响，并在临时表被删除后再次变得可访问。

历史说明：临时表是在 MySQL 3.23 版本中引入的。在现代数据库开发中，它们是一个标准且广泛使用的功能。

创建临时表

创建临时表几乎与创建永久表相同，只是多了一个 TEMPORARY 关键字。

语法

CREATE TEMPORARY TABLE table_name (
    column1_definition,
    column2_definition,
    ...
);

示例：存储顶级客户

想象一下您有一个 sales 表，并且您希望对本月表现最佳的客户运行几个复杂查询。与其为每个查询重新计算顶级客户列表，不如将它们存储在临时表中。

首先，假设我们有一个永久的 sales 表：

CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    sale_date DATE NOT NULL
);

INSERT INTO sales (customer_id, amount, sale_date) VALUES
(101, 50.00, '2023-10-01'),
(102, 150.00, '2023-10-02'),
(101, 200.00, '2023-10-05'),
(103, 75.00, '2023-10-06'),
(102, 300.00, '2023-10-10');

现在，创建一个临时表来存储每个客户的总销售额并填充它。

-- 1. 创建临时表
CREATE TEMPORARY TABLE TopCustomers (
    customer_id INT PRIMARY KEY,
    total_spent DECIMAL(12, 2) NOT NULL
);

-- 2. 从永久表中填充数据
INSERT INTO TopCustomers (customer_id, total_spent)
SELECT customer_id, SUM(amount)
FROM sales
WHERE sale_date >= '2023-10-01'
GROUP BY customer_id
ORDER BY SUM(amount) DESC;

您现在可以像对待常规表一样，对这个 TopCustomers 表执行任何操作。

SELECT * FROM TopCustomers;

customer_id	total_spent
102	450.00
101	250.00
103	75.00

注意：临时表不会出现在 SHOW TABLES 的输出中，除非您使用的是非常旧的 MySQL 版本。要检查它是否存在，可以尝试对其进行 SELECT 查询，或者使用 DESCRIBE TopCustomers;。

删除临时表

虽然临时表在会话结束时会自动删除，但为了释放资源，尤其是在长时间运行的脚本中，在您完成使用后显式删除它们是良好的实践。

语法

使用 DROP TEMPORARY TABLE。建议包含 IF EXISTS 以防止因表不存在而导致的错误。

DROP TEMPORARY TABLE IF EXISTS TopCustomers;

运行此命令后，尝试查询该表将导致错误。

SELECT * FROM TopCustomers;
-- 错误 1146 (42S02): 表 'your_database.TopCustomers' 不存在

在应用程序代码中使用临时表

在应用程序中使用临时表时，所有相关操作（CREATE、INSERT、SELECT、DROP）都必须在同一个数据库连接上进行。如果您使用连接池（connection pool），则必须确保获取单个连接并在临时表的整个生命周期内都使用它。

Python 示例（演示会话范围）

import mysql.connector

DB_CONFIG = {
    'user': 'root',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database'
}

def temp_table_workflow():
    # 所有操作必须使用同一个连接对象
    with mysql.connector.connect(**DB_CONFIG) as connection:
        print("连接已建立。")
        with connection.cursor() as cursor:
            print("正在创建临时表...")
            cursor.execute("""
                CREATE TEMPORARY TABLE SessionData (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    message VARCHAR(255)
                );
            """)

            print("正在向临时表插入数据...")
            cursor.execute("INSERT INTO SessionData (message) VALUES (%s)", ('Hello, from this session!',))
            connection.commit()

            print("正在查询临时表:")
            cursor.execute("SELECT * FROM SessionData;")
            for row in cursor.fetchall():
                print(f"  - 行: {row}")

            print("正在显式删除临时表。")
            cursor.execute("DROP TEMPORARY TABLE IF EXISTS SessionData;")

    print("连接已关闭。临时表已消失。")

# --- 运行工作流 ---
temp_table_workflow()

Node.js 示例（使用单个连接）

const mysql = require('mysql2/promise');

async function tempTableWorkflow() {
    let connection;
    try {
        // 对于临时表，获取单个连接，而不是来自连接池
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'your_password',
            database: 'your_database'
        });
        console.log('连接已建立。');

        console.log('正在创建临时表...');
        await connection.execute(`
            CREATE TEMPORARY TABLE UserActions (
                action_id INT AUTO_INCREMENT PRIMARY KEY,
                action_type VARCHAR(50),
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
            );
        `);

        console.log('正在插入数据...');
        await connection.execute(`INSERT INTO UserActions (action_type) VALUES (?), (?)`, ['login', 'view_page']);

        console.log('正在查询临时表:');
        const [rows] = await connection.execute('SELECT * FROM UserActions ORDER BY action_id;');
        console.log(rows);

    } catch (error) {
        console.error('工作流失败:', error);
    } finally {
        if (connection) {
            await connection.end();
            console.log('连接已关闭，临时表已销毁。');
        }
    }
}

tempTableWorkflow();

性能与存储

MySQL 会智能地处理临时表的存储引擎：

• 内存中（In-Memory）： 默认情况下，临时表使用 InnoDB 或 MyISAM 引擎（取决于默认设置）创建，但 MySQL 可能会为了提高速度而将它们保存在内存中。如果表变得太大（超出 tmp_table_size 和 max_heap_table_size 限制），它将自动转换为磁盘格式。
• ENGINE=MEMORY： 您可以使用 CREATE TEMPORARY TABLE ... ENGINE=MEMORY; 显式创建内存临时表。这些表速度非常快，但不能包含 BLOB 或 TEXT 列。

对于大多数用例，让 MySQL 管理存储就足够了。性能提升来自于减少复杂的计算，并拥有一个更小、已索引的数据集，以便进行后续查询。