数据库——临时表[通俗易懂]

大家好，欢迎来到IT知识分享网。

一、临时表的概念
 在我们操作的表数据量庞大而且又要关联其他表进行查询的时候或者我们操作的数据是临时性数据且在短期内会有很多DML操作(比如购物车)的时候或者我们做查询时需要连接很多个表的时候，如果直接操作数据库的业务表可能效率很低，这个时候我们就可以借助临时表来提升效率。
 临时表顾名思义，是一个临时的表，数据库不会将其序列化到磁盘上(有些也会序列化到磁盘上)而是存在于数据库服务器的内存中(因此会增加数据库服务器内存的消耗)，在使用完之后就会销毁。临时表分为两种：会话临时表和全局临时表，区别在于可用的作用域和销毁的时机不同。会话临时表只在当前会话(连接)内可用，且在当前会话结束(断开数据库连接)后就会销毁；全局临时表创建后在销毁之前所有用户都可以访问，销毁的时机是在创建该临时表的连接断开且没有其他会话访问时才销毁，实际上在创建全局临时表的会话断开后，其他用户就已经不能在访问该临时表了，但此时该临时表并不会立即销毁，而是等所有正在使用该全局临时表的会话(或者说连接)断开之后才会销毁。当然有时考虑到内存占用的问题，我们也可以手动销毁(DROP)临时表。
 目前大多数数据库厂商(Oracle、Sql Server、Mysql)都支持临时表，但不同的数据库创建和使用临时表的语法稍有不同。

二、临时表的创建、使用和删除
 1、SQL Server
  创建：
   方式一：

#会话临时表
CREATE TABLE #临时表名(
	字段1 约束条件1,
	字段2 约束条件2,
	...
);

#全局临时表
CREATE TABLE ##临时表名(
	字段1 约束条件,
	字段2 约束条件,
	...
);

   方式二：

#会话临时表
SELECT 字段列表 INTO #临时表名 
FROM 业务表;

#全局临时表
SELECT 字段列表 INTO ##临时表名 
FROM 业务表;

  使用：

#查询临时表
SELECT * FROM #临时表名;
SELECT * FROM ##临时表名;

  删除：

#删除临时表
DROP TABLE #临时表名;
DROP TABLE ##临时表名;

 2、Mysql
  创建：Mysql中没有全局临时表，创建的时候没有#

CREATE TEMPORARY TABLE [IF NOT EXISTS] 临时表名(
	字段1 约束条件,
	字段2 约束条件,
	...
);

#根据现有表创建临时表
CREATE TEMPORARY TABLE [IF NOT EXISTS] 临时表名
[AS] SELECT 查询字段
FROM 业务表
[WHERE 条件];

  使用：创建的临时表可以和业务表同名，若临时表和业务表同名时在该会话中会使用临时表

SELECT * FROM 临时表名;

  删除：为避免临时表名和业务表名相同时导致误删除，可以加上TEMPORARY关键字

DROP [TEMPORARY] TABLE 临时表名;

 3、Oracle
  Oracle的临时表也只有会话级的，但同时又细化出了一个事务级别的临时表，事务级别的临时表只在当前事务中有效。
  创建：

#会话级别
CREATE GLOBAL TEMPORARY TABLE 临时表名(
	字段1 约束条件,
	字段2 约束条件,
	...
) ON COMMIT PRESERVE ROWS;

#事务级别
CREATE GLOBAL TEMPORARY TABLE 临时表名(
	字段1 约束条件,
	字段2 约束条件,
	...
) ON COMMIT DELETE ROWS;

  使用：

SELECT * FROM 临时表名;

  删除：

DROP TABLE 临时表名;

 注意：一个SQL中不能同时出现两次临时表

三、临时表的应用
 企业开发中大多都是使用Spring进行事务管理的，很少自己开启事务、提交事务。我们大多都会将事务加在service层，这样在调用service层的每一个方法之前Spring都会为我们开启事务，在方法调用结束之后Spring会为我们提交事务，问题是数据库事务需要的数据库连接是在什么时候获取和释放的呢？这个是会影响我们对临时表的使用的。
 一般来说，数据库连接是在事务开启之前获取的，也就是在我们调用事务方法之前，肯定要先获取数据库连接，然后才能开启事务，提交或回滚事务，然后关闭数据库连接，这种情况下貌似如果我们在该方法中创建了临时表，则在此之后直至方法结束之前我们都可以使用这个创建的临时表，这么说基本上是正确的。但有一种情况除外那就是如果我们在事务方法A中调用了另一个事务方法B，而事务方法B的事务传播机制是PROPAGATION_REQUIRES_NEW(将原事务挂起，并新开一个事务)时，如果临时表是在B方法中创建的，则A在调用完B之后(B的事务已经提交了)也不可以使用B中创建的事务级别的临时表，但是可以使用会话级别的临时表以及全局临时表。