MySQL系列教程 --- 20、MySQL 临时表（Temporary）的操作

MySQL 具有创建称为临时表的特殊表的功能，该表允许我们保留临时数据。我们可以在特定会话中多次重复使用此表。它在 MySQL 中可供3.23及以上版本的用户使用，因此如果我们使用旧版本，则无法使用此表。此表仅对当前会话可见和可访问。只要当前会话关闭或用户终止连接，MySQL 就会自动删除此表。当用户不打算使用它时，我们还可以使用DROP TABLE命令显式删除该表。

如果我们使用PHP脚本来运行代码，只要脚本完成执行，该表就会自动删除。如果用户通过 MySQL 客户端与服务器连接，则该表将一直存在，直到用户关闭 MySQL 客户端程序或终止连接或手动删除该表。

临时表提供了一个非常有用和灵活的特性，使我们能够快速完成复杂的任务，例如当我们查询需要带有JOIN子句的单个SELECT 语句的数据时。在这里，用户可以使用这个表来保存输出并执行另一个查询来处理它。

MySQL 中的临时表有很多特性，具体如下：

• MySQL 使用 CREATE TEMPORARY TABLE 语句来创建临时表。
• 该语句只能在 MySQL 服务器具有 CREATE TEMPORARY TABLES 权限时使用。
• 创建它的客户端可以看到和访问它，这意味着两个不同的客户端可以使用同名的临时表而不会相互冲突。因为这个表只有创建它的那个客户端才能看到。因此，用户不能在同一个会话中创建两个同名的临时表。
• 当用户关闭会话或手动终止连接时，MySQL 中的临时表将自动删除。
• 用户可以创建一个与数据库中普通表同名的临时表。例如，如果用户创建了一个名为 student 的临时表，那么现有的 student 表将无法访问。因此，用户对学生表执行任何查询，现在将引用临时学生表。当用户删除临时表时，永久学生表将再次可访问。

创建临时表的语法

在 MySQL 中，除了 TEMPORARY 关键字外，创建临时表的语法与创建普通表语句的语法相同。让我们看看以下创建临时表的语句：

mysql> CREATE TEMPORARY TABLE table_name (  
   column_1, column_2, ..., table_constraints  
);  

如果用户要创建一个临时表，其结构与数据库中现有表的结构相同，则不能使用上述语句。相反，我们使用下面给出的语法：

Mysql> CREATE TEMPORARY TABLE temporary_table_name SELECT * FROM original_table_name LIMIT 0; 

MySQL 临时表示例

让我们了解如何在 MySQL 中创建临时表。执行以下语句，在所选数据库中创建临时表：

mysql> CREATE TEMPORARY TABLE Students( student_name VARCHAR(40) NOT NULL, total_marks DECIMAL(12,2) NOT NULL DEFAULT 0.00, total_subjects INT UNSIGNED NOT NULL DEFAULT 0);  

我们可以看到下图：

接下来，我们需要在临时表中插入值：

mysql>INSERT INTO Students(student_name, total_marks, total_subjects) VALUES ('Joseph', 150.75, 2), ('Peter', 180.75, 2);  

执行上述语句后，它将给出以下输出：

现在，运行以下查询以获取结果：

mysql> SELECT * FROM Students;  

成功执行上述语句后，我们将得到如下输出：

需要注意的是，当我们运行SHOW TABLES命令时，我们的临时表将不会显示在列表中。另外，如果我们关闭当前会话，然后再执行SELECT语句，我们会得到一条消息，说数据库中没有可用的数据，甚至临时表也不存在。

临时表，其结构基于普通表

在本例中，我们将创建一个临时表，其结构基于数据库中已有的表。

假设我们的数据库有以下永久表：

这里使用SELECT语句创建一个临时表的结构，并使用INNER JOIN子句合并两个表并根据价格对它们进行排序。在 MySQL 提示符中写入以下语句：

CREATE TEMPORARY TABLE temp_customers  
SELECT c.cust_name, c.city, o.prod_name, o.price   
FROM orders o  
INNER JOIN customer c ON c.cust_id = o.order_id  
ORDER BY o.price DESC;  

当我们执行上述语句时，我们将得到以下消息：

现在，运行以下命令查看临时表：

mysql> SELECT * FROM temp_customers;  

我们也可以从上面的临时表“ temp_customers ”执行查询，类似于从永久表查询数据。以下查询更清楚地解释了它：

Mysql> SELECT cust_name, prod_name, price FROM temp_customers;  

执行上述语句后，它将给出如下输出：

注意：请注意，我们可以使用 IF NOT EXISTS 关键字来避免“表已存在”错误。

如何删除 MySQL 中的临时表

MySQL 允许我们使用DROP TABLE语句删除临时表。但是，在 DROP TABLE 语句中使用TEMPORARY关键字是一个很好的做法。这个关键字帮助我们避免在当前会话中临时表和永久表同名时删除永久表的错误。因此，它建议使用以下查询删除临时表：

mysql> DROP TEMPORARY TABLE table_name;  

此查询不会删除数据库的永久表，这意味着它只会删除临时表。如果我们尝试用这个语句删除一个永久表，它会抛出一个错误消息，说你正在删除一个未知的表。比如我们要删除上面的临时表“temp_customers”，就需要使用如下语句：

mysql> DROP TEMPORARY TABLE top_customers;