MySQL 制作临时表并从中选取数据

在本文中，我们将介绍如何在MySQL中制作临时表并从中选取数据。临时表是在需要时动态创建的表，其数据存在于MySQL服务器的临时数据库中。使用临时表可以提高查询性能，减少对表的压力。

阅读更多：MySQL 教程

创建临时表

MySQL中创建临时表的语法为：

CREATE TEMPORARY TABLE table_name (
 column_name1 datatype,
 column_name2 datatype,
 ...
) ENGINE=engine_name;

其中，table_name为临时表的名称，column_name为表中列的名称，datatype为列的数据类型。ENGINE为可选参数，用于指定表的存储引擎。

例如，创建一个包含姓名和年龄的临时表的语句为：

CREATE TEMPORARY TABLE temp_table (
 name VARCHAR(30),
 age INT
) ENGINE=InnoDB;

向临时表中插入数据

向临时表中插入数据的方法与向普通表中插入数据的方法类似，可以使用insert into语句。例如：

INSERT INTO temp_table (name, age) VALUES
 ('Alice', 20),
 ('Bob', 30),
 ('Charlie', 25),
 ('David', 28);

从临时表中选取数据

从临时表中选取数据的方法与从普通表中选取数据的方法类似，可以使用select语句。例如：

SELECT * FROM temp_table;

输出为：

+---------+-----+
| name    | age |
+---------+-----+
| Alice   |  20 |
| Bob     |  30 |
| Charlie |  25 |
| David   |  28 |
+---------+-----+

临时表的作用

临时表具有以下几个优点：

改善查询性能

在某些场景下，为了避免查询结果被影响，需要将查询结果保存到临时表中。在下一次查询时，只需要查询临时表即可，这样可以避免每次重复查询。例如：

CREATE TEMPORARY TABLE temp_result
SELECT col1, col2, col3
FROM origin_data
WHERE col1 > 100;

SELECT * FROM temp_result;

对复杂查询进行优化

在某些复杂查询场景下，可能需要多次使用相同的查询结果。如果这种查询结果比较复杂，查询时间很长，可以将其保存到临时表中，以便以后使用。例如：

CREATE TEMPORARY TABLE temp_query1_result
SELECT col1, col2, col3
FROM origin_data
WHERE col1 > 100;

CREATE TEMPORARY TABLE temp_query2_result
SELECT col1, col2, col3
FROM temp_query1_result
WHERE col2 < 50;

SELECT * FROM temp_query2_result;

上例中，第二次查询依赖于第一次查询的结果。如果重复执行第一次查询，则会浪费大量时间，而将其结果保存到临时表中，可以节省查询时间。

支持递归查询

在某些场景下，需要对数据进行递归查询，例如寻找一篇文章的所有评论。使用临时表可以方便地进行递归查询。例如：

CREATE TEMPORARY TABLE temp_comment (id INT, parent_id INT, content TEXT);

INSERT INTO temp_comment (id, parent_id, content) VALUES
 (1, 0, '这是文章正文'),
 (2, 1, '这是第一条评论'),
 (3, 1, '这是第二条评论'),
 (4, 2, '这是第一个评论的回复'),
 (5, 3, '这是第二个评论的回复'),
 (6, 4, '这是第一个回复的回复');

WITH recursive_query (id, parent_id, depth, path,content) AS (
 SELECT id, parent_id, 0 as depth, CAST(id AS CHAR(200)) as path, content
 FROM temp_comment 
 WHERE parent_id = 0

 UNION ALL

 SELECT c.id, c.parent_id, rq.depth+1, CONCAT_WS(',', path, c.id), c.content
 FROM temp_comment AS c
 JOIN recursive_query AS rq ON c.parent_id = rq.id
)
SELECT * FROM recursive_query;

上述例子使用了递归查询，在WITH语句中创建了一个recursive_query的临时表，其中parent_id=0表示为top-level评论，每次递归时都按照path变成一个由当前查询记录id和上级path组成的逗号分隔字符串，以便之后的访问、按照depth排序或许多其他目的。