MySQL临时表的使用方法详解

在写查询时我们会经常用到临时表来存储数据,下面这篇文章主要给大家介绍了关于MySQL临时表的使用方法,文中通过示例代码介绍的非常详细,需要的朋友可以参考下

1. 写在前面的话

在开发数据库时，特别是写存储过程，遇到比较复杂的需求，使用临时表可以简化很多逻辑。曾经在一家互联网金融公司供职，公司数据组团队做数据清洗，写SQL脚本时，一个查询语句可以套到数层查询，甚至十几层。看起来几百行上千行的脚本，其实他只是一个查询，就是说是一个select基于另一个select的结果。这样层层叠叠，同时还包含了聚合、排序、关联、联合等，看起来是相当的费劲，头疼。

就如下列样式：

 SELECT T1.A1, T1.A2, T1.A3... FROM ( SELECT T2.B1, T2.B2, T2.B3... FROM ( SELECT ... UNION ALL SELECT ...) WHERE ... GROUP BY ...) T1, ( SELECT T3.C1, T3.C2, T3.C3... FROM ( SELECT ...) WHERE ... GROUP BY ...) T3 WHERE T1.A1 = T3.C1...

如果这里能使用临时表，那么就会使逻辑清晰很多，查询效率也会得到提升。比如在多处使用到同一个查询的结果时，就可以只执行一次查询，将结果保存为临时表，在查询过程中每次使用到时，直接从临时表查就可以了，不用每次使用都再去查询一遍原始数据，尤其是对于复杂关联查询结果。

当时数据是来源于呼叫中心，每天都会产生几百万行数据，一个月就上亿行的数据量，就当时的机器性能基础上，在这个数据量来做统计操作，如果没有高效的脚本，是非常耗时间的。同时，数据组职员并没有专业数据库设计能力。

本文以数据库World为例进行说明。

2. 临时表的使用

World数据库中数据表country放着全世界国家的相关信息。

2.1 创建一个只存放亚洲国家信息的临时表

2.1.1 创建临时表

创建一个只存放亚洲国家信息的临时表，命名为TempAsiaCountries。
这里临时只取四个字段Code,Name,SufaceArea,Population，那么创建临时表的脚本如下：

# 创建临时表 CREATE TEMPORARY TABLE TempAsiaCountries ( `Code` VARCHAR (10), `name` VARCHAR (30), `SufaceArea` NUMERIC, `Population` NUMERIC );

创建完之后，会发现在数据库的表目录下并没有这个表，因为表是临时的，所以不会出现在表目录里。

2.1.2 向临时表里写数据

如同写实体表数据一样，可以通过INSERT INTO 关键字进行表插入数据操作。

# 写数据到临时表 INSERT INTO TempAsiaCountries SELECT `Code`, `Name`, `SurfaceArea`, `Population` FROM country WHERE `Continent` = 'Asia';

此时，可以通过SELECT操作查询临时表里的数据。

2.2 在查询过程中直接创建临时表

在实际使用临时表时，可以更快速便捷的创建临时表，如下脚本，直接以查询结果创建临时表的方法。

CREATE TEMPORARY TABLE TempAsiaCountries SELECT `Code`, `Name`, `SurfaceArea`, `Population` FROM country WHERE `Continent` = 'Asia';

2.3 查询临时表中的数据

对临时表的查询操作与实体表一样，如下是查询前文中所创建的临时表中的数据

# 查询临时表 SELECT * FROM TempAsiaCountries;

可以看到查询结果

2.4 删除临时表

# 删除临时表 DROP TABLE TempAsiaCountries;

此时再查，就会返回表不存在的结果

3. 以上操作的全部代码

# 创建临时表 CREATE TEMPORARY TABLE TempAsiaCountries ( `Code` VARCHAR (10), `name` VARCHAR (30), `SufaceArea` NUMERIC, `Population` NUMERIC ); # 第一种 # 写数据到临时表 INSERT INTO TempAsiaCountries SELECT `Code`, `Name`, `SurfaceArea`, `Population` FROM country WHERE `Continent` = 'Asia'; # 查询临时表 SELECT * FROM TempAsiaCountries; # 第二种 # 查询数据并写到临时表中 CREATE TEMPORARY TABLE TempAsiaCountries SELECT `Code`, `Name`, `SurfaceArea`, `Population` FROM country WHERE `Continent` = 'Asia'; # 删除临时表 DROP TABLE TempAsiaCountries;