MySQL 临时表

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

MySQL临时表介绍

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

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

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

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

即使临时表可以与永久表同名，也不推荐这样做。因为这可能会导致混淆并可能导致意外的数据丢失。

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

MySQL `CREATE TEMPORARY TABLE` 语句

在语法 CREATE TEMPORARY TABLE staetment是类似的语法， CREATE TABLE 除了声明 TEMPORARY 关键字：

CREATE TEMPORARY TABLE table_name(
   column_1_definition,
   column_2_definition,
   ...,
   table_constraints
);

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

CREATE TEMPORARY TABLE temp_table_name
SELECT * FROM original_table
LIMIT 0;

1) 创建临时表实例

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

CREATE TEMPORARY TABLE credits(
    customerNumber INT PRIMARY KEY,
    creditLimit DEC(10,2)
);

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

INSERT INTO credits(customerNumber,creditLimit)
SELECT customerNumber, creditLimit
FROM customers
WHERE creditLimit > 0;

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;

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

SELECT 
    customerNumber, 
    customerName, 
    sales
FROM
    top_customers
ORDER BY sales;

删除 MySQL 临时表

您可以使用该 DROP TABLE 语句删除临时表，但是添加 TEMPORARY 关键字是一种很好的做法，如下所示：

DROP TEMPORARY TABLE table_name;

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

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

DROP TEMPORARY TABLE top_customers;

请注意，如果您尝试使用该 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 ;

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

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

CALL check_table_exists('credits');
SELECT @table_exists;

这是输出：

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