mysql临时表用法分析【查询结果可存在临时表中】

让我们从头开始了解一下mysql临时表的用法和使用场景。

什么是临时表？

临时表是一种特殊的表，只在当前会话内存在，一旦会话关闭，临时表就会被自动删除。临时表的优点是：
- 可以提高MySQL实例的并发处理能力；
- 减轻锁竞争，提升查询效率；
- 临时表可以存储查询结果，增加查询灵活性

临时表的创建

在MySQL中，创建临时表分为两种方式：
- 使用CREATE TEMPORARY TABLE语句
- 在SELECT语句中使用SELECT INTO语句

使用CREATE TEMPORARY TABLE语句

CREATE TEMPORARY TABLE tmptable (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(20) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

使用SELECT INTO语句

此方法的好处是不需要在前面准备好临时表结构。

SELECT * INTO TEMPORARY TABLE tmptable
FROM mytable
WHERE id > 100;

临时表的使用

使用临时表最大的优势在于大量数据的操作处理，可以把查询结果存入一个临时表中，然后根据需要再使用相应的查询语句对结果进行操作。

示例1：查询结果可存在临时表中

以下是查询一张表中的数据，将查询结果存入一个临时表中，并使用临时表的查询结果：

创建临时表

CREATE TEMPORARY TABLE `temp_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

将查询结果存入临时表

INSERT INTO `temp_table` (`id`, `name`, `age`)
SELECT * FROM `origin_table`;

使用临时表查询

SELECT `name`, `age` FROM `temp_table` WHERE `age` > 18;

示例2：使用临时表对两张表进行连接查询

以下是使用临时表对两张表进行连接查询的例子：

创建临时表

CREATE TEMPORARY TABLE `temp_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

将查询结果存入临时表

INSERT INTO `temp_table` (`id`, `name`, `age`)
SELECT `id`, `name`, `age` FROM `user_table`;

查询临时表数据和另外一张表的数据

SELECT `temp_table`.`name`, `blog_table`.`title` FROM `temp_table`
LEFT JOIN `blog_table` ON `temp_table`.`id` = `blog_table`.`user_id`;

以上两个示例展示了临时表的使用方法，可以看到临时表在高并发的场景下起到了很大的作用。当然，临时表的使用需要满足一定的条件，如操作数量过小，则无法体现其优越性。