Skip to main content
  1. 经验技巧分享/
  2. thinking in SQL | 废话SQL,思维与实践/

Pseudo column | 什么是伪列

··501 字
sql 概念

伪列(pseudo column)、生成列(generated column)、虚拟列(virtual column),傻傻分不清楚。

生成列(generated column)
数据库中使用列约束为列定义计算生成逻辑的列,也叫计算列。
虚拟列(virtual column)
SQL:2003 标准中的定义,它也是生成列,只有一部分关系型数据库有实现。它特指没有保存值、仅在使用时再计算的生成列。因此它是生成列的一种形式。
伪列(pseudo column)
伪列指实际不存在列。即可查询某个表时,在选择列表中使用到了表中没有字段,并且还有数据。有些数据库,把伪列称为系统列。因中文中,虚拟与伪均有“假”、“不真实存在”之意,故容易混淆伪列与虚拟列。但从定义上,还是可以辨别。

若为方便记忆,可先忽略虚拟列,把它当作没有保存值的生成列即可。对生成列感兴趣的,可看

各数据库中的伪列 #

上面提到的伪列也称系统列,它是数据库专有的,不通用,看看都有哪些伪列可以使用,其中属 oracle 提供的伪列较为丰富。

oracle 数据库中常用伪列 #

文档链接 👉 Pseudocolumns

伪列 用途说明
ROWID 数据行逻辑位置,由数据对象、数据文件、文件中数据块、数据块中的行,可以非常快速定位记录。
ROWNUM 查询结果集的行编号,可用于过滤 topN
CONNECT_BY_ISCYCLE CONNECT BY 递归查询的专有伪列,表示是否循环,若是为1
CONNECT_BY_ISLEAF CONNECT BY 递归查询的专有伪列,表示是否末端的节点,若是为1
LEVEL CONNECT BY 递归查询的专有伪列,表示当前层次,可用于控制递归深度
CURRVAL, nextval 序列专有伪列,用法 序列名.currval序列名.nextval
ORA_ROWSCN system change-number(SCN)。表示数据的修改时点,可以转为时间戳。可以通过它来查找之前某个时点的历史版本。当然,需要 oracle 数据库日志中记录的信息来支持。有时,不小心把数据删除或者修改了,可以通过它来找出历史版本手工恢复。

postgres 数据库中的系统列(伪列) #

文档链接 👉 System Columns

tableoid
表的 OID。该列对于从分区表或 继承层次结构 中进行选择的查询特别方便。没有它,很难判断一行来自哪个单独的表。 tableoid 可以与 pg_class 的 oid 列连接以获得表名。
xmin
此行版本的插入事务的标识(事务 ID)。 (行版本是行的单独状态;行的每次更新都会为同一逻辑行创建一个新的行版本。) The identity (transaction ID) of the inserting transaction for this row version. (A row version is an individual state of a row; each update of a row creates a new row version for the same logical row.)
xmax
删除事务的标识(事务 ID),或者对于未删除的行版本为零。在可见行版本中,此列可能不为零。这通常表明删除事务尚未提交,或者尝试的删除已回滚。

cmin :插入事务中的命令标识符(从零开始)

cmax
删除事务中的命令标识符,或零。
ctid
行版本在其表中的物理位置。请注意,尽管 ctid 可用于非常快速地定位行版本,但如果行的 ctid 被 VACUUM FULL 更新或移动,则会发生变化。因此 ctid 作为长期行标识符是无用的。应该使用主键来标识逻辑行。
-- sakila 示例数据库中的 address 表
select tableoid, xmin, xmax, cmax, cmin, ctid, address
from   address
limit 10

上面 sql 中的 tableoid、xmin、xmax、cmax、cmin、ctid 均非 address 表中的字段。

 tableoid | xmin  | xmax  | cmax | cmin |  ctid  |             address
----------+-------+-------+------+------+--------+--------------------------------
    46733 | 29966 | 29967 |    0 |    0 | (0,1)  | 47 MySakila Drive
    46733 | 29966 | 29967 |    0 |    0 | (0,2)  | 28 MySQL Boulevard
    46733 | 29966 | 29969 |    0 |    0 | (0,3)  | 23 Workhaven Lane
    46733 | 29966 | 29970 |    0 |    0 | (0,4)  | 1411 Lillydale Drive
    46733 | 29966 | 29968 |    0 |    0 | (0,5)  | 1913 Hanoi Way
    46733 | 29966 | 29968 |    0 |    0 | (0,6)  | 1121 Loja Avenue
    46733 | 29966 | 29968 |    0 |    0 | (0,7)  | 692 Joliet Street
    46733 | 29966 | 29968 |    0 |    0 | (0,8)  | 1566 Inegl Manor
    46733 | 29966 | 29968 |    0 |    0 | (0,9)  | 53 Idfu Parkway
    46733 | 29966 | 29968 |    0 |    0 | (0,10) | 1795 Santiago de Compostela Way

其它数据库的伪列 #

sqlite3 rowid
文档链接 👉 Rowid Tables 。sqlite3 中,所有的实体表都是 “rowid table”,除非在建表时声明了 without rowid。通过 rowid 来定位数据行的效率非常高,可以使用 rowidoid_rowid_ 这三个名称来引用。
-- sakila 示例数据库中的 address 表
select rowid, oid , _rowid_ , address from address limit 10;
| rowid | rowid | rowid |             address             |
|-------|-------|-------|---------------------------------|
| 1     | 1     | 1     | 47 MySakila Drive               |
| 2     | 2     | 2     | 28 MySQL Boulevard              |
| 3     | 3     | 3     | 23 Workhaven Lane               |
| 4     | 4     | 4     | 1411 Lillydale Drive            |
| 5     | 5     | 5     | 1913 Hanoi Way                  |
| 6     | 6     | 6     | 1121 Loja Avenue                |
| 7     | 7     | 7     | 692 Joliet Street               |
| 8     | 8     | 8     | 1566 Inegl Manor                |
| 9     | 9     | 9     | 53 Idfu Parkway                 |
| 10    | 10    | 10    | 1795 Santiago de Compostela Way |
mysql _rowid
在 mysql 中,只要表的主键是 int 类数字类型,那么就可以使用 _rowid ,其值为该主键字段的值。

本文使用了 sakila 示例数据库 中的 address 表。