Skip to main content

MySQL临时表|创建、使用和删除MySQL临时表

MySQL临时表简介

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

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

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

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

MySQL创建临时表语句

CREATE TEMPORATE TABLE语句的语法与CREATE TABLE语句的语法相似,只是临时关键字不同:

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

要创建结构基于现有表的临时表,不能使用“创建临时表…”。。。像是声明。而是使用以下语法:

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 Temporary Table Example

删除MySQL临时表

可以使用DROP TABLE语句删除临时表,但最好按如下方式添加临时关键字:

DROP TEMPORARY TABLE table_name;

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

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

DROP TEMPORARY TABLE top_customers;

请注意,如果您试图使用DROP TEMPORATE 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来检查临时表是否存在:

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

以下是输出:

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