MySQL Temporary Table

摘要：在本教程中，我们将讨论MySQL 临时表并向您展示如何创建、使用和删除临时表。

MySQL临时表简介

在 MySQL 中，临时表是一种特殊类型的表，它允许您存储临时结果集，您可以在单个会话中多次重复使用该结果集。

当不可能或昂贵地查询需要带有JOIN子句的单个SELECT语句的数据时，临时表非常方便。在这种情况下，您可以使用临时表来存储即时结果并使用另一个查询来处理它。

MySQL 临时表具有以下特殊功能：

• 临时表是使用CREATE TEMPORARY TABLE语句创建的。请注意，关键字TEMPORARY添加在CREATE和TABLE关键字之间。
• 当会话结束或连接终止时，MySQL 会自动删除临时表。当然，当不再使用临时表时，可以使用DROP TABLE语句显式删除它。
• 临时表仅可供创建它的客户端使用和访问。不同的客户端可以创建同名的临时表而不会导致错误，因为只有创建临时表的客户端才能看到它。但是，在同一个会话中，两个临时表不能共享相同的名称。
• 临时表可以与数据库中的普通表同名。例如，如果您在示例数据库中创建名为employees的临时表，则现有employees表将无法访问。您对employees表发出的每个查询现在都引用临时表employees 。当您删除employees临时表时，永久employees表将可用且可访问。

例如，如果与数据库服务器的连接丢失并且您自动重新连接到服务器，则无法区分临时表和永久表。然后，您可能会发出DROP TABLE语句来删除永久表而不是临时表，这是不期望的。为了避免此问题，您可以使用DROP TEMPORARY TABLE语句删除临时表。

MySQL CREATE TEMPORARY TABLE语句

除了TEMPORARY关键字之外CREATE TEMPORARY TABLE语句的语法与CREATE TABLE语句的语法类似：

CREATE TEMPORARY TABLE table_name(
   column_1_definition,
   column_2_definition,
   ...,
   table_constraints
);Code language: SQL (Structured Query Language) (sql)

要创建其结构基于现有表的临时表，不能使用CREATE TEMPORARY TABLE ... LIKE语句。相反，您可以使用以下语法：

CREATE TEMPORARY TABLE temp_table_name
SELECT * FROM original_table
LIMIT 0;Code language: SQL (Structured Query Language) (sql)

1）创建临时表示例

首先，创建一个名为credits新临时表，用于存储客户的积分：

CREATE TEMPORARY TABLE credits(
    customerNumber INT PRIMARY KEY,
    creditLimit DEC(10,2)
);Code language: SQL (Structured Query Language) (sql)

然后，将customers表中的行插入到临时表credits中：

INSERT INTO credits(customerNumber,creditLimit)
SELECT customerNumber, creditLimit
FROM customers
WHERE creditLimit > 0;Code language: SQL (Structured Query Language) (sql)

2）创建临时表，其结构基于查询示例

以下示例创建一个临时表，用于存储按收入排名前 10 位的客户。临时表的结构源自SELECT语句：

CREATE TEMPORARY TABLE top_customers
SELECT p.customerNumber, 
       c.customerName, 
       ROUND(SUM(p.amount),2) sales
FROM payments p
INNER JOIN customers c ON c.customerNumber = p.customerNumber
GROUP BY p.customerNumber
ORDER BY sales DESC
LIMIT 10;Code language: SQL (Structured Query Language) (sql)

现在，您可以像从永久表中查询一样从top_customers临时表中查询数据：

SELECT 
    customerNumber, 
    customerName, 
    sales
FROM
    top_customers
ORDER BY sales;Code language: SQL (Structured Query Language) (sql)

删除 MySQL 临时表

您可以使用DROP TABLE语句删除临时表，但最好添加TEMPORARY关键字，如下所示：

DROP TEMPORARY TABLE table_name;Code language: SQL (Structured Query Language) (sql)

DROP TEMPORARY TABLE语句仅删除临时表，而不删除永久表。当您将临时表命名为与永久表的名称相同时，它可以帮助您避免删除永久表的错误

例如，要删除topcustomers临时表，请使用以下语句：

DROP TEMPORARY TABLE top_customers;Code language: SQL (Structured Query Language) (sql)

请注意，如果您尝试使用DROP TEMPORARY TABLE语句删除永久表，您将收到一条错误消息，指出您尝试删除的表未知。

如果您开发的应用程序使用连接池或持久连接，则不能保证在应用程序终止时自动删除临时表。因为应用程序使用的数据库连接可能仍处于打开状态，并放置在连接池中以供其他客户端稍后重用。因此，每当不再使用临时表时，最好始终删除它们。

检查临时表是否存在

MySQL不提供直接检查临时表是否存在的函数或语句。但是，我们可以创建一个存储过程来检查临时表是否存在，如下所示：

DELIMITER //
CREATE PROCEDURE check_table_exists(table_name VARCHAR(100)) 
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @err = 1;
    SET @err = 0;
    SET @table_name = table_name;
    SET @sql_query = CONCAT('SELECT 1 FROM ',@table_name);
    PREPARE stmt1 FROM @sql_query;
    IF (@err = 1) THEN
        SET @table_exists = 0;
    ELSE
        SET @table_exists = 1;
        DEALLOCATE PREPARE stmt1;
    END IF;
END //
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

在此过程中，我们尝试从临时表中选择数据。如果临时表存在，则@table_exists变量设置为1，否则设置为0。

该语句调用check_table_exists来检查临时表credits是否存在：

CALL check_table_exists('credits');
SELECT @table_exists;
Code language: SQL (Structured Query Language) (sql)

这是输出：

在本教程中，您了解了 MySQL 临时表以及如何管理临时表，例如创建和删除新的临时表。