SQL 中的生成列/计算列以及主流数据库实现

2020-03-07 投稿人 : www.gay9991.cn 围观 : 1759 次

什么是文章目录中生成的列?在数据库中的虚拟列在数据库服务器中的计算列在服务器中的计算列在服务器中的计算列在服务器中的计算列在服务器中的计算列在服务器中的计算列在服务器中的计算列在服务器中的计算列在服务器中的计算列。

在SQL数据库中,“生成的列”是指由表中其他字段计算的列,因此也称为“计算的列”。

有两种类型的生成列:存储生成列和虚拟生成列。存储生成列类似于普通列。插入或更新数据时,会自动计算并存储列的值,这需要存储空间。虚拟生成的列不需要占用存储空间,仅在读取时计算。因此,虚拟生成的列类似于视图(字段的视图),而存储生成的列类似于实体化视图(实时更新)。

我们不能直接插入或更新生成列的值。其值由数据库自动生成和更新。

generated columns的常见用途包括:

virtual generated columns可用于简化和统一查询。我们可以将复杂的查询条件定义为生成的列,然后在查询表时使用它,从而确保所有的查询都使用相同的判断条件。

存储物化缓存,其中生成的列可用作查询条件,以降低查询过程中的计算成本。

构建列模拟函数索引:基于函数表达式定义构建列并创建索引。对于存储类型生成的列,此方法需要更多存储。

各种主流的SQL数据库对生成/计算列的支持如下:

Store生成列

Store生成列

Store生成列

Store生成列

*请参考下面的具体讨论。

*请参考下面的具体讨论。

*请参考下面的具体讨论。

甲骨文

*请参考下面的具体讨论。

甲骨文

*请参考下面的具体讨论。

甲骨文

甲骨文

*请参考下面的具体讨论。

甲骨文

*请参考下面的具体讨论。

甲骨文

甲骨文

*请参考下面的具体讨论。

甲骨文

*请参考下面的具体讨论。

甲骨文

*请参考下面的具体讨论。

甲骨文

*请参考下面的具体讨论。

甲骨文

*请参考下面的具体讨论。

甲骨文

*请参考下面的具体讨论。

甲骨文

甲骨文

甲骨文11g中的虚拟列开始支持虚拟生成列,简称虚拟列。定义虚拟列的语法如下:

甲骨文11g中的虚拟列开始支持虚拟生成列,简称虚拟列。定义虚拟列的语法如下:

首先,使用语句创建一个虚拟列周长;对于表t _ circle然后使用语句向其中添加一个虚拟列区域。

接下来我们插入一些数据:

第一个insert语句没有指定虚拟列的值,该值在查询时由数据库自动计算;第二个insert语句指定虚拟列的值,但执行失败。

甲骨文支持索引中的虚拟列。我们在t_circle中为虚拟列创建了两个索引:

除了支持索引,虚拟列还支持、主键和外键约束,但不支持默认值。

使用甲骨文虚拟列时应注意以下事项:

如果表达式column_expression引用了具有列级安全性的字段,则虚拟列不会继承基础列的安全性规则。此时,用户需要自己确保虚拟列数据的安全性,并且可以为虚拟列设置另一个列级安全策略或使用函数来模糊数据。例如,通常使用列级安全策略来包含信用卡号码,该策略允许客户服务中心员工查看最后四位数字来验证信息。此时,可以定义一个虚拟列来保存信用卡号的最后四个子字符串。

基于虚拟列创建的索引等同于函数索引。

虚拟列不能直接更新。因此,不能在语句的子句中设置虚拟列。但是,虚拟列可以用在语句的子句中。同样,虚拟列可以用在语句的子句中。

使用子句中包含虚拟列的表的查询语句来缓存结果,请参阅甲骨文官方文档了解详细信息。

表达式column_expression可以引用一个显式指定确定性属性的PL/SQL函数。但是,如果函数的定义随后被替换,基于虚拟列的对象不会失败。此时,如果表包含数据,并且虚拟列用于约束、索引、实体化视图或查询结果缓存,则访问虚拟列的查询可能会返回不正确的结果。因此,为了替换虚拟列中的确定性函数:

禁用并启用对虚拟列的约束。

在虚拟列上重建索引。

基于虚拟列完全刷新物化视图。

刷新访问虚拟列的查询结果缓存。

再次收集表格的统计数据。

虚拟列可以是不可见列,虚拟列的表达式可以包含不可见列。

甲骨文中的虚拟列有以下限制:

虚拟列只能为关系堆表创建。索引组织表、外部表、对象表、聚集表和临时表不支持虚拟列。

虚拟列表达式column_expression有以下限制:

不能引用其他虚拟列。

只能引用当前表中的列。

可以引用确定性的自定义函数,但该虚拟列此时不能用作分区字段。

表达式的结果必须是标量值。

虚拟列不支持Oracle提供的数据类型、用户定义的类型以及LOB和LONG RAW类型。

虚拟列可以用作分区字段,但是作为分区字段的虚拟列不能包含PL/SQL函数。

参考文件:甲骨文官方文件。

MySQL中的构建列

MySQL 5.7引入了构建列,它支持虚拟和存储类型的构建列。定义生成列的语法如下:

其中,可以省略,定义生成列的表达式。指示创建虚拟生成的列,并且不会存储虚拟列的值,但会在读取触发器后立即计算。表示存储生成列;默认创建是生成列。

我们创建一个表t _ circle:

其中perimeter是一个虚拟构建列;区域是一个存储的生成列。MySQL生成的列还支持、主键和外键约束,但不支持默认值。

MySQL必须遵循以下规则来生成列表达式:

常量、确定性内置函数和运算符是允许的。确定性函数意味着对于表中的相同数据,不管当前用户是谁,多个调用都会返回相同的结果。非确定性函数包括连接标识()、当前用户()、现在()等。

不允许存储和自定义函数。

不允许存储过程和函数参数。

变量(系统变量、自定义变量或存储程序中的局部变量)是不允许的。

不允许子查询。

允许引用表中已定义的其他生成的列;允许引用任何其他未生成的列,无论它们出现在前面还是后面。不允许使用

AUTO_INCREMENT属性。

AUTO _ INVATION字段不允许作为生成列的基列。

CREATE TABLE将失败,如果表达式的操作导致截断或向函数提供不正确的输入。

此外,如果表达式的结果类型不同于字段定义中的数据类型,将执行隐式类型转换。

接下来我们运行一些数据测试:

第一个insert语句没有指定生成列的值,该值由数据库自动计算;第二个insert语句提供了周界数据,但未能执行。但是,可以使用关键字。

MySQL支持存储生成列的索引,InnoDB也支持虚拟生成列的二级索引。详情请参考MySQL官方文件。我们使用下面的语句为t_circle表的两个生成的列创建两个索引:

另外,在使用MySQL生成列时应该注意以下事项:

对于该语句,原始表中生成的列信息将保留在新创建的表中。

对于语句,创建的新表不会保留查询语句的原始表中生成的列信息;And语句不能为目标表中生成的列赋值。

允许基于生成的列进行分区,详情请参考MySQL官方文档。

存储生成列上的外键约束不能指定、或

对于、和,如果要显式指定生成的列的值,则只能使用。视图中生成的列属于可更新的列,但只能使用显式更新。

参考文件:MySQL官方文件。

SQL Server

SQL Server 2005中的计算列增加了对生成列的支持,称为计算列。计算列的完整定义如下:

其中,它意味着定义计算列;指示需要存储此列的值,即存储类型的计算列;计算列的表达式可以使用其他非计算列、常数、函数、变量,但不能使用子查询或别名数据类型。SQL Server中的计算列支持主键和约束,存储的计算列也支持外键和约束。但是,计算列不支持默认值,不能用作外键中的引用字段。

我们创建一个表t _ circle:

其中perimeter是一个虚拟计算列;面积是存储的计算列;语句添加第三个计算列,并使用不确定性函数GETDATE()。

让我们测试数据插入和查询:

第一个插入语句没有指定生成列的值,该值由数据库自动计算;查询返回了所有字段。如果运行多次,dt字段将返回不同的日期。第二个insert语句提供了周界数据,但未能执行。和语句不能为生成的列指定值。

SQL Server支持基于计算列的索引,但需要满足某些条件:

前两个计算列不包含不确定的函数,可以创建索引,或者主键和唯一约束。但是,dt列不支持索引,因为它包含不确定的函数,并且它的值可能会在每次调用时发生变化。

参考文档:SQL Server正式文档。

PostgreSQL

PostgreSQL 12中存储的生成列提供了生成列,目前只支持存储类型的生成列。通过在or语句中指定字段的约束来创建生成列:

其中,这意味着创建一个生成列;Generation_expr指定生成列的表达式;这意味着需要存储该列的值。例如,下面的语句:

首先,该语句为表t_circle定义一个生成列周长,它代表圆的周长。然后,使用语句添加一个生成的列区域来表示圆的面积。

接下来我们插入一些数据:

第一个insert语句没有指定生成列的值,而是由数据库自动计算的;第二个insert语句提供了周界数据,但未能执行。和语句不能为生成的列指定值,但可以使用关键字。

PostgreSQL中生成的列支持索引。我们使用下面的语句为t_circle表的两个生成的列创建两个索引:

第一个索引idx1是唯一索引,第二个索引idx2是普通索引。

PostgreSQL生成的列除了支持索引之外,还支持、主键和外键约束,但不支持默认值。此外,在PostgreSQL中使用生成的列有一些限制:

生成列的表达式只能使用不可变函数,不能使用子查询或引用不是当前数据行的任何其他数据。生成列的

表达式不能引用其他生成的列。

生成列的表达式不能引用tableoid以外的系统字段。

生成的列不能指定默认值或标识列。

生成的列不能是分区键的一部分。

外部表可以支持生成列,请参考创建外部表。

使用生成列时,还应注意以下几点:

生成列的访问控制独立于其表达式中引用的基础列。因此,用户可能无法读取基础列中的数据,但可以读取生成的列中的数据,以实现特定的数据安全访问。

从逻辑上讲,生成的列的值在BEFORE触发器之后更新。对前置触发器中基础列的修改与生成的列同步;但是,另一方面,生成的列的值不能在BEFORE触发器中访问。

参考文档:PostgreSQL生成列。

SQLite

SQLite 3.31.0中生成的列开始支持生成的列。在语法上,它们是通过字段约束“GENERATED ALIGHT”实现的:

可以省略;表示存储类型生成列和虚拟类型基因

接下来我们插入一些数据:

第一个insert语句已成功执行,查询返回了生成的两列的值;第二个insert语句试图指定生成的列的值,但返回了一个错误。

SQLite中的生成列支持以下功能:

生成列可以指定数据类型。SQLite使用与普通字段相同的类型相似性将表达式的结构转换为该类型。

生成的列可以像普通字段一样指定非空、检查、唯一和外键约束。

生成的列可以像普通字段一样支持索引。

生成列的表达式可以引用表中的任何列,包括其他生成的列,只要该表达式不直接或间接引用自己。

生成的列可以出现在表定义的任何地方。生成的列可以出现在普通列的中间,而不必出现在字段列表的末尾。

我们可以使用下面的语句为t_circle表的两个生成列创建两个索引:

另一方面,SQLite中的生成列目前有一些限制:

生成的列不能指定默认值。生成的列的值总是由AS关键字后的表达式决定。

生成的列不能是主键的一部分。未来的SQLite可能支持基于STORED为列生成主键。

生成列的表达式只能引用常量和其他字段,或者确定性标量函数。表达式中不能使用子查询、聚合函数、窗口函数或表函数。

生成列的表达式可以引用其他生成的列,但不能引用直接或间接依赖于自身的其他生成的列。

生成列的表达式不能直接引用ROWID,但可以引用INTEGER PRIMARY KEY列,尽管两者具有相同的效果。

每个表至少需要包含一个未生成的普通列。

ALTER TABLE ADD COLUMN语句不支持存储生成列,但可以添加虚拟生成列。

generated列的数据类型和字符排序顺序由字段定义和COLLATE子句中的数据类型决定,与GENERATED

ALWAYS表达式的数据类型和字符排序顺序无关。

参考文件:SQLite正式文件。

欢迎关注、赞美和前进!