跟我一起学【PostgreSQL】之——临时表(temporary table)的特性和用法

本文对比了PostgreSQL和Oracle中临时表的特性,包括会话隔离性、易挥发性和数据易挥发性。PostgreSQL的临时表在会话和事务层面提供了更严格的隔离,且可通过选项控制其生命周期。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

熟悉Oracle的人,相比对临时表(temporary table)并不陌生,很多场景对解决问题起到不错的作用,开源库Postgresql中,也有临时表的概念,虽然和Oracle中临时表名字相同,使用方法和特性也有类似的地方,但还是有很多不同的方面,下面就对比Oracle中临时表举例说明下。

1、Postgresql 临时表的会话隔离性

Oracle中的临时表,创建后,虽然各个会话间的数据是互相隔离的,也就是一个会话看不到其他会话的数据,但定义是共用的。而Postgresql中的临时表,创建后,不但不同会话间的数据是相互隔离的,就连临时表的定义也是不同会话间相互隔离的,也就是一个会话创建的临时表,不能被其他会话看到,如下图所示:

session1:创建了临时表t_tmp。

session2:以另一个会话登录相同用户名和数据库,查看session1中创建的临时表,既不能查询表,也不能查看表结构,但可以通过系统视图看到该临时表的存在。

2、Posgresql临时表的易挥发性

Oracle中的临时表,一个会话的数据在会话退出时会自动消失,但临时表的定义一旦创建,就会已会一直存在,知道用户删除。而Postgresql的临时表不是这样,虽然会话退出会话数据也会自动消失,但定义也会随着会话的退出而消失,也就是说,Postgresql中的临时表的生命周期最长就是会话生命周期,甚至更短,如图所示:

上图是前面session1退出会话后,再次登录查询之前创建的临时表t_tmp,已经查不到了,哪怕是临时表定义也没了。

此外,在创建Postgresql临时表时,还可以设置相关选项,使得临时表在事务结束时消失,哪怕会话没结束,也会消失,如图所示:

上图中,虽然创建临时表成功了,但回头查看时却怎么也看不到表定义,虽然接连创建了两边,还是看不到表定义,也不能查,原因就是定义临时表是用了on commit drop选项,这意味着事务结束时,自动删除临时表,本人用的psql客户端工具中,每当完成一个ddl或dml时,会默认认为事务结束,所以,创建临时表的ddl语句被认为一个事务,虽然创建语句成功了,表也创建了,但随后,事务结束后被很快又删除了,所以,无论如何也看不到临时表的定义。但如果显式声明一个事务的开始和结束,即使使用了on commit drop选项,创建临时表后,也是可以看到定义的,插入数据后,也一样可以看到数据,如下图所示:

然后,使用on commit drop选项创建的临时表,一旦创建它的事务结束,临时表和其中的数据也就消失了,如下图所示:

3、Postgresql临时表数据的易挥发性

Oracle中临时表的数据,会随着会话事务或会话的结束而自动消失,主要看创建临时表时的相关选项。Postgresql临时表中的数据,也有类似的功能,除了通过on commit drop选项可以设置事务结束临时表就消失外,还可以通过on commit相关选项,分别控制临时表的数据在事务结束消失(仅仅数据消失,定义还存在)和会话结束消失(数据和表定义都消失),如下通过on commit delete rows定义了临时表:

上图可以看到,虽然成功创建了临时表,也往其中成功插入了数据,可是再查询数据时,虽然表定义还在,但数据没了,这是因为定义时用了on commit delete rows选项,就是事务结束,数据就消失,这里的insert into语句默认为一个事务,执行完事务就算结束,所以,插入的数据立刻又消失了,如下图,显式的定义了事务的开始和结束,事务期间,插入的数据可以查到:

但一旦事务结束,这些刚刚插入临时表的数据又立刻不见了,如下图:

此外,还可以用on commit preserve rows选项来定义临时表,通过该选项定义的临时表,事务结束时,数据依然还会存在,直到会话结束为止,如下图所示:

可见,单个语句的insert事务结束后,依然可以查到数据的存在,这也是临时表定义不设置on commit选项时的默认行为。

原文链接:https://www.cnblogs.com/lhdz_bj/p/9024183.html

<think>我们正在处理用户关于创建临时表的SQL问题。用户明确要求创建一个与现有表结构相同临时表,并给出了示例语法:CREATE TEMPORARY TABLE bd_vendor_temp LIKE bd_vendor 根据引用内容: [^1]提到创建临时表的第二种方法是使用CREATE TABLE语句定义结构,再插入数据。但用户的需求是直接复制现有表结构,不需要插入数据。 [^2]说明了临时表特性:仅当前会话可见,会话结束删除,且不支持分区索引。 [^3][^4]提到了MySQL使用临时表的一些情况,但与我们当前操作关系不大。 因此,我们需要指导用户使用正确的语法创建临时表,并注意临时表的限制。 步骤: 1. 使用CREATE TEMPORARY TABLE ... LIKE语句可以复制现有表的结构(包括列定义,但不包括索引约束?注意引用[2]提到临时表不支持创建索引)。 2. 但注意,在MySQL中,使用LIKE创建临时表会复制原表的列定义,但不会复制原表的索引、约束等(因为临时表不支持索引)。而在其他数据库如SQL Server,语法可能不同。 由于用户给出的示例是MySQL语法(因为引用了MySQL的文档),我们以MySQL为例。 在MySQL中,创建临时表的正确语法为: ```sql CREATE TEMPORARY TABLE bd_vendor_temp LIKE bd_vendor; ``` 但是,需要注意的是,原表bd_vendor如果有索引,那么临时表bd_vendor_temp并不会创建这些索引(因为临时表不支持索引,如引用[2]所述)。如果原表有索引,那么在创建临时表后,需要单独添加必要的约束(如主键)?但临时表不支持索引,所以不能添加。 另外,用户也可以使用CREATE TEMPORARY TABLE ... AS SELECT ... 的方式,但这样会复制数据,而用户只需要结构。 因此,我们给出两种方式: - 仅复制结构:`CREATE TEMPORARY TABLE ... LIKE ...` - 复制结构数据:`CREATE TEMPORARY TABLE ... AS SELECT ...` 但用户明确要求“与现有表结构相同”,且没有提到数据,所以第一种方式更合适。 同时,提醒用户临时表的生命周期限制。 回答结构: 1. 直接给出用户需要的SQL语句。 2. 解释该语句的作用注意事项。 3. 提供另一种方式(如果需要)并比较。 4. 提醒临时表的限制(如会话结束自动删除,不支持索引等)。 根据引用[2],临时表会话结束时自动删除,且如果与普通表同名,则优先使用临时表。 因此,回答如下:</think>在 SQL 中创建与现有表结构相同临时表,可以使用 `CREATE TEMPORARY TABLE ... LIKE` 语句。针对您的需求,具体操作如下: ### 创建临时表的语法 ```sql CREATE TEMPORARY TABLE bd_vendor_temp LIKE bd_vendor; ``` ### 关键说明 1. **结构复制** `LIKE` 子句会精确复制原表 `bd_vendor` 的列定义(包括数据类型、约束等),但**不会复制以下内容**: - 索引(如 PRIMARY KEY/UNIQUE 索引) - 外键约束 - 表分区设置 - 数据存储参数(如 InnoDB 引擎配置)[^2] 2. **临时表特性** - **会话隔离**:仅对当前数据库会话可见,其他会话无法访问[^2] - **自动清理**:会话结束(连接关闭)时自动删除表及数据[^2] - **命名优先级**:若与普通表同名,会话内优先访问临时表[^2] 3. **限制注意事项** - 不支持表分区字段 - 无法创建索引(需手动添加) - 存储引擎限制(如 MEMORY 引擎不支持 TEXT/BLOB 类型)[^2][^4] ### 扩展应用 若需同时复制数据,可追加 `INSERT` 语句: ```sql INSERT INTO bd_vendor_temp SELECT * FROM bd_vendor WHERE [条件]; ``` ### 不同数据库的语法差异 | 数据库 | 语法示例 | |--------------|--------------------------------------------------------------------------| | **MySQL** | `CREATE TEMPORARY TABLE bd_vendor_temp LIKE bd_vendor;` | | **SQL Server** | `SELECT * INTO #bd_vendor_temp FROM bd_vendor WHERE 1=0;`(仅结构) | | **PostgreSQL** | `CREATE TEMPORARY TABLE bd_vendor_temp (LIKE bd_vendor INCLUDING DEFAULTS);` | > ⚠️ 重要提示:临时表操作应避免在事务中过度使用,可能引发锁竞争或临时表空间膨胀[^3][^4]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值