在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

SQL 中的生成列/計算列以及主流數(shù)據(jù)庫實現(xiàn)

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目錄

        什么是生成列？
        Oracle 中的虛擬列
        MySQL 中的生成列
        SQL Server 中的計算列
        PostgreSQL 中的存儲生成列
        SQLite 中的生成列

生成列
什么是生成列？

在 SQL 數(shù)據(jù)庫中，生成列（Generated Column）是指由表中其他字段計算得到的列，因此也稱為計算列（Computed Column）。

生成列存在兩種類型：存儲（stored）生成列和虛擬（virtual）生成列。存儲生成列和普通列類似，在插入或者更新數(shù)據(jù)時自動計算并且存儲該列的值，需要占用存儲空間；虛擬生成列不需要占用存儲空間，只在讀取時進行計算。因此，虛擬生成列就像是一個視圖（字段的視圖），而存儲生成列就像是一個物化視圖（實時更新）。

??我們無法直接插入或者更新生成列的值，它的值由數(shù)據(jù)庫自動生成和更新。

生成列的常見用途包括：

虛擬生成列可以用來簡化和統(tǒng)一查詢。我們可以將復雜的查詢條件定義成一個生成列，然后在查詢該表時使用，從而確保所有的查詢都使用相同的判斷條件。
存儲生成列可以作為查詢條件的物化緩存（materialized cache），減少查詢時的計算成本。
生成列可以模擬函數(shù)索引：定義一個基于函數(shù)表達式的生成列并且創(chuàng)建索引。對于存儲型的生成列，這種方式需要占用更多的存儲。

各種主流 SQL 數(shù)據(jù)庫對于生成列/計算列的支持如下：

• 參考下文中的具體討論。
  Oracle 中的虛擬列

Oracle 11g 開始支持虛擬的生成列，簡稱虛擬列。定義虛擬列的語法如下：

column [ datatype [ COLLATE column_collation_name ] ]
[ GENERATED ALWAYS ] AS (column_expression) [ VIRTUAL ]

如果省略了 datatype，虛擬列的類型由表達式 column_expression 決定；GENERATED ALWAYS AS表示定義生成列；表達式只能包含當前表中的字段、常量以及確定性的函數(shù)；VIRTUAL表示虛擬列，可以省略。以下是一個創(chuàng)建虛擬列的示例：

CREATE TABLE t_circle(
id INTEGER PRIMARY KEY,
x NUMERIC(20,10) NOT NULL,
y NUMERIC(20,10) NOT NULL,
radius NUMERIC(20,10) NOT NULL,
perimeter NUMERIC(20,10) GENERATED ALWAYS AS (2 * 3.14159265 * radius) VIRTUAL
);

ALTER TABLE t_circle ADD (area AS (3.14159265 * radius * radius));

首先，使用CREATE TABLE語句為表 t_circle 創(chuàng)建了一個虛擬列 perimeter；然后使用ALTER TABLE語句為其增加了一個虛擬列 area。

接下來我們插入一些數(shù)據(jù)：

INSERT INTO t_circle(id, x, y, radius) VALUES (1, 2, 2, 5);
SELECT * FROM t_circle;

INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
SQL Error [54013] [99999]: ORA-54013: INSERT operation disallowed on virtual columns

第一個插入語句沒有指定虛擬列的值，在查詢時由數(shù)據(jù)庫自動計算；第二個插入語句指定了虛擬列的值，執(zhí)行失敗。

Oracle 中的虛擬列支持索引，我們?yōu)?t_circle 中的虛擬列創(chuàng)建兩個索引：

CREATE UNIQUE INDEX idx11 ON t_circle(perimeter);
CREATE INDEX idx12 ON t_circle(area);

除了支持索引之外，虛擬列還支持NOT NULL、UNIQUE、主鍵、CHECK以及外鍵約束，但是不支持DEFAULT默認值。

使用 Oracle 虛擬列需要注意以下事項：

如果表達式 column_expression 引用了具有列級安全的字段，虛擬列不會繼承基礎列上的安全規(guī)則。此時，用戶需要自己確保虛擬列數(shù)據(jù)的安全，可以為虛擬列再設置一個列級安全策略或者使用函數(shù)對數(shù)據(jù)進行模糊處理。例如，信用卡號通常會使用一個列級安全策略進行包含，允許客服中心的員工查看最后四位數(shù)字以便驗證信息。此時，可以定義一個虛擬列保存信用卡號的后四位子串。
基于虛擬列創(chuàng)建的索引等價于函數(shù)索引。
不能直接更新虛擬列。因此，不能在UPDATE語句的SET子句中設置虛擬列。不過，可以在UPDATE語句的WHERE子句中使用虛擬列。同理，可以在DELETE語句的WHERE子句中使用虛擬列。
在FROM子句中使用包含虛擬列的表的查詢語句可以緩存結果，具體參考Oracle 官方文檔。
表達式 column_expression 可以引用明確指定了 DETERMINISTIC 屬性的 PL/SQL 函數(shù)。但是，如果隨后替換了該函數(shù)的定義，基于虛擬列的對象不會失效。此時如果表中包含數(shù)據(jù)，而且虛擬列用于了約束、索引、物化視圖或者查詢結果緩存，訪問虛擬列的查詢可能返回不正確的結果。因此，為了替換虛擬列中的確定性函數(shù)：
    禁用然后再啟用虛擬列上的約束。
    重建虛擬列上的索引。
    完全刷新基于虛擬列的物化視圖。
    刷新訪問了該虛擬列的查詢結果緩存。
    重新收集該表的統(tǒng)計信息。
虛擬列可以是 INVISIBLE 列，虛擬列的表達式中可以包含 INVISIBLE 列。

Oracle 中的虛擬列存在以下限制：

只能為關系型的堆表創(chuàng)建虛擬列，索引組織表、外部表、對象表、聚簇表以及臨時表不支持虛擬列。
虛擬列表達式 column_expression 存在以下限制：
    不能引用其他虛擬列。
    只能引用當前表中的列。
    可以引用確定性的自定義函數(shù)，但此時該虛擬列不能作為分區(qū)字段。
    表達式的結果必須是一個標量值。
虛擬列不支持 Oracle 提供的數(shù)據(jù)類型、用戶定義類型以及 LOB 和 LONG RAW 類型。
虛擬列可以作為分區(qū)字段，但是作為分區(qū)字段的虛擬列中不能包含 PL/SQL 函數(shù)。

參考文檔：Oracle 官方文檔。
MySQL 中的生成列

MySQL 5.7 引入了生成列，支持虛擬和存儲兩種類型的生成列。定義生成列的語法如下：

col_name data_type [GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT ‘string’]

其中，GENERATED ALWAYS可以省略，AS定義了生成列的表達式；VIRTUAL表示創(chuàng)建虛擬生成列，虛擬列的值不會存儲，而是在讀取時BEFORE觸發(fā)器之后立即計算；STORED表示存儲生成列；默認創(chuàng)建的是VIRTUAL生成列。

我們創(chuàng)建一個表 t_circle：

CREATE TABLE t_circle(
id INTEGER PRIMARY KEY,
x NUMERIC(20,10) NOT NULL,
y NUMERIC(20,10) NOT NULL,
radius NUMERIC(20,10) NOT NULL,
perimeter NUMERIC(20,10) AS (2 * 3.14159265 * radius)
);

ALTER TABLE t_circle ADD area NUMERIC(20,10) AS (3.14159265 * radius * radius) STORED;

其中，perimeter 是一個虛擬的生成列；area 是一個存儲的生成列。MySQL 生成列還支持NOT NULL、UNIQUE、主鍵、CHECK以及外鍵約束，但是不支持DEFAULT默認值。

MySQL 生成列的表達式必須遵循以下規(guī)則：

允許使用常量、確定性的內(nèi)置函數(shù)以及運算符。確定性函數(shù)意味著對于表中的相同數(shù)據(jù)，多次調(diào)用返回相同的結果，與當前用戶無關。非確定性的函數(shù)包括 CONNECTION_ID()、CURRENT_USER()、NOW() 等。
不允許使用存儲函數(shù)和自定義函數(shù)。
不允許使用存儲過程和函數(shù)的參數(shù)。
不允許使用變量（系統(tǒng)變量、自定義變量或者存儲程序中的本地變量）。
不允許子查詢。
允許引用表中已經(jīng)定義的其他生成列；允許引用任何其他非生成列，無論這些列出現(xiàn)的位置在前面還是后面。
不允許使用 AUTO_INCREMENT 屬性。
不允許使用 AUTO_INCREMENT 字段作為生成列的基礎列。
如果表達式的運算導致截斷或者為函數(shù)提供了不正確的輸入，CREATE TABLE 將會失敗。

另外，如果表達式的結果類型與字段定義中的數(shù)據(jù)類型不同，將會執(zhí)行隱式的類型轉換。

接下來我們運行一些數(shù)據(jù)測試：

INSERT INTO t_circle(id, x, y, radius) VALUES (1, 2, 2, 5);
SELECT * FROM t_circle;

INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
SQL Error [3105] [HY000]: The value specified for generated column ‘perimeter’ in table ‘t_circle’ is not allowed.

第一個插入語句沒有指定生成列的值，由數(shù)據(jù)庫自動計算；第二個插入語句為 perimeter 提供了數(shù)據(jù)，執(zhí)行失??；不過可以使用DEFAULT關鍵字。

MySQL 支持存儲生成列的索引，InnoDB 還支持虛擬生成列的二級索引，具體參考 MySQL 官方文檔 。我們使用以下語句為 t_circle 表的兩個生成列分別創(chuàng)建兩個索引：

CREATE UNIQUE INDEX idx1 ON t_circle(perimeter);
CREATE INDEX idx2 ON t_circle(area);

另外，使用 MySQL 生成列還需要注意以下事項：

對于CREATE TABLE ... LIKE語句，創(chuàng)建的新表將會保留原表中的生成列信息。
對于CREATE TABLE ... SELECT語句，創(chuàng)建的新表不會保留查詢語句的原表中的生成列信息；并且SELECT語句不能為目標表中的生成列賦值。
允許基于生成列的分區(qū)，具體參考 MySQL 官方文檔。
存儲生成列上的外鍵約束不能為ON UPDATE操作指定CASCADE、SET NULL或者SET DEFAULT選項，也不能為ON DELETE操作指定SET NULL 或者SET DEFAULT選項。
存儲生成列的基礎列上的外鍵約束也不能為ON UPDATE或者ON DELETE操作指定CASCADE、SET NULL或者SET DEFAULT選項。
外鍵約束中的被引用字段不能是虛擬的生成列。
觸發(fā)器不能通過 NEW.col_name 或者 OLD.col_name 引用生成列。
對于INSERT、REPLACE以及UPDATE，如果要明確指定生成列的值，只能使用DEFAULT。視圖中的生成列屬于可更新列，但是也只能使用DEFAULT顯式更新。

參考文檔：MySQL 官方文檔。
SQL Server 中的計算列

SQL Server 2005 增加了生成列的支持，稱為計算列。計算列的完整定義如下：

<computed_column_definition> ::=
column_name AS computed_column_expression
[ PERSISTED [ NOT NULL ] ]
[
[ CONSTRAINT constraint_name ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , …n ] )
]
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | “default” } ]

| [ FOREIGN KEY ]
    REFERENCES referenced_table_name [ ( ref_column ) ]
    [ ON DELETE { NO ACTION | CASCADE } ]
    [ ON UPDATE { NO ACTION } ]
    [ NOT FOR REPLICATION ]

| CHECK [ NOT FOR REPLICATION ] ( logical_expression )

]

其中，AS表示定義一個計算列；PERSISTED表示需要存儲該列的值，即存儲型的計算列； 計算列的表達式可以使用其他非計算列、常量、函數(shù)、變量，但是不能使用子查詢或別名數(shù)據(jù)類型。SQL Server 中的計算列支持主鍵、UNIQUE約束，存儲計算列還支持NOT NULL、外鍵以及CHECK約束；但是計算列不支持DEFAULT默認值，也不能作為外鍵中的被引用字段。

我們創(chuàng)建一個表 t_circle：

CREATE TABLE t_circle(
id INTEGER PRIMARY KEY,
x NUMERIC NOT NULL,
y NUMERIC NOT NULL,
radius NUMERIC NOT NULL,
perimeter NUMERIC AS (2 * 3.14159265 * radius),
area NUMERIC AS (3.14159265 * radius * radius) PERSISTED
);

ALTER TABLE t_circle ADD dt AS GETDATE();

其中，perimeter 是一個虛擬的計算列；area 是一個存儲的計算列；ALTER TABLE語句增加了第三個計算列，使用了一個不確定性函數(shù) GETDATE()。

我們測試一下數(shù)據(jù)插入和查詢：

INSERT INTO t_circle VALUES (1, 2, 2, 5);
SELECT * FROM t_circle;

INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
SQL Error [271] [S0001]: The column “perimeter” cannot be modified because it is either a computed column or is the result of a UNION operator.

第一個插入語句沒有指定生成列的值，由數(shù)據(jù)庫自動計算；查詢返回了所有的字段，多次運行的話 dt 字段將會返回不同的日期；第二個插入語句為 perimeter 提供了數(shù)據(jù)，執(zhí)行失?。籌NSERT和UPDATE語句不能為生成列指定值。

SQL Server 支持基于計算列的索引，但是需要滿足一定的條件：

create unique index idx1 on t_circle(perimeter);
create index idx2 on t_circle(area);

create index idx3 on t_circle(dt);
SQL Error [2729] [S0001]: Column ‘dt’ in table ‘t_circle’ cannot be used in an index or statistics or as a partition key because it is non-deterministic.

前兩個計算列不包含不確定性的函數(shù)，可以創(chuàng)建索引，或者 PRIMARY KEY 和 UNIQUE 約束；但是 dt 列不支持索引，因為它包含了不確定性的函數(shù)，每次調(diào)用時它的值可能發(fā)生變化。

參考文檔：SQL Server 官方文檔。
PostgreSQL 中的存儲生成列

PostgreSQL 12 提供了生成列，目前只支持存儲型的生成列。通過在CREATE TABLE或者ALTER TABLE語句中指定字段的GENERATED ALWAYS AS約束來創(chuàng)建一個生成列：

column_name data_type [ COLLATE collation ]
[ CONSTRAINT constraint_name ]
GENERATED ALWAYS AS ( generation_expr ) STORED

其中，GENERATED ALWAYS AS表示創(chuàng)建生成列；generation_expr 指定了生成列的表達式；STORED意味著需要存儲該列的值。例如以下語句：

CREATE TABLE t_circle(
id INTEGER PRIMARY KEY,
x NUMERIC NOT NULL,
y NUMERIC NOT NULL,
radius NUMERIC NOT NULL,
perimeter NUMERIC GENERATED ALWAYS AS (2 * 3.14159265 * radius) STORED
);

ALTER TABLE t_circle ADD area NUMERIC GENERATED ALWAYS AS (3.14159265 * radius * radius) STORED;

首先，CREATE TABLE語句為表 t_circle 定義了一個生成列 perimeter，表示圓的周長。然后，使用ALTER TABLE語句增加一個生成列 area ，表示圓的面積。

接下來我們插入一些數(shù)據(jù)：

INSERT INTO t_circle VALUES (1, 2, 2, 5);
SELECT * FROM t_circle;

INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
SQL Error [42601]: ERROR: cannot insert into column “perimeter”
Detail: Column “perimeter” is a generated column.

第一個插入語句沒有指定生成列的值，由數(shù)據(jù)庫自動計算；第二個插入語句為 perimeter 提供了數(shù)據(jù)，執(zhí)行失?。籌NSERT和UPDATE語句不能為生成列指定值，不過可以使用DEFAULT關鍵字。

PostgreSQL 中的生成列支持索引，我們使用以下語句為 t_circle 表的兩個生成列分別創(chuàng)建兩個索引：

create unique index idx1 on t_circle(perimeter);
create index idx2 on t_circle(area);

第一個索引 idx1 是唯一索引，第二個索引 idx2 是普通索引。

除了支持索引之外，PostgreSQL 生成列還支持NOT NULL、UNIQUE、主鍵、CHECK以及外鍵約束，但是不支持DEFAULT默認值。另外，在 PostgreSQL 中使用生成列存在一些限制：

生成列的表達式只能使用不可變（IMMUTABLE）函數(shù)，不能使用子查詢或者引用非當前數(shù)據(jù)行的任何其他數(shù)據(jù)。
生成列的表達式不能引用其他的生成列。
生成列的表達式不能引用除了 tableoid 之外的其他系統(tǒng)字段。
生成列不能指定默認值或者標識列。
生成列不能作為分區(qū)鍵的一部分。
外部表可以支持生成列，參考 CREATE FOREIGN TABLE。

使用生成列時還需要注意以下事項：

生成列的訪問權限控制與其表達式中引用的基礎列無關。因此，一個用戶可能無法讀取基礎列中的數(shù)據(jù)，但是可以讀取生成列的數(shù)據(jù)，實現(xiàn)特定的數(shù)據(jù)安全訪問。
從邏輯上講，生成列的值在 BEFORE 觸發(fā)器之后進行更新。在 BEFORE 觸發(fā)器中對基礎列的修改會同步到生成列中；但是反過來，在 BEFORE 觸發(fā)器中不能訪問生成列的值。

參考文檔：PostgreSQL 生成列。
SQLite 中的生成列

SQLite 3.31.0 開始支持生成列，語法上通過“GENERATED ALWAYS”字段約束實現(xiàn)：

其中的GENERATED ALWAYS可以省略；STORED表示存儲型的生成列，VIRTUAL表示虛擬型的生成列，省略的話默認為后者。例如以下示例表：

CREATE TABLE t_circle(
id INTEGER PRIMARY KEY,
x NUMERIC NOT NULL,
y NUMERIC NOT NULL,
radius NUMERIC NOT NULL,
perimeter NUMERIC GENERATED ALWAYS AS (2 * 3.14159265 * radius) VIRTUAL,
area NUMERIC GENERATED ALWAYS AS (3.14159265 * radius * radius) STORED
);

其中 radius 表示圓的半徑；perimeter 是一個虛擬生成列，表示圓的周長；area 是一個存儲生成列，表示圓的面積。

??SQLite 中的ALTER TABLE ADD COLUMN命令只能增加VIRTUAL生成列，不支持STORED生成列。

接下來我們插入一些數(shù)據(jù)：

sqlite> INSERT INTO t_circle VALUES (1, 2, 2, 5);
sqlite> SELECT * FROM t_circle;
1|2|2|5|31.4159265|78.53981625

sqlite> INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
Error: cannot INSERT into generated column “perimeter”

第一個插入語句執(zhí)行成功，查詢返回了兩個生成列的值；第二個插入語句嘗試指定生成列的值，返回了錯誤。

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

生成列可以指定數(shù)據(jù)類型。SQLite 使用與普通字段相同的類型親和性將表達式的結構轉換為該類型。
生成列可以像普通字段一樣指定 NOT NULL、CHECK 以及 UNIQUE 約束和外鍵約束。
生成列可以像普通字段一樣支持索引。
生成列的表達式可以引用表中的任何列，包括其他的生成列，只要該表達式不會直接或者間接引用自身。
生成列可以出現(xiàn)在表定義中的任何位置。生成列可以出現(xiàn)在普通列的中間，不一定需要位于字段列表的最后。

我們可以使用以下語句為 t_circle 表的兩個生成列分別創(chuàng)建兩個索引：

sqlite> create unique index idx1 on t_circle(perimeter);
sqlite> create index idx2 on t_circle(area);

另一方面，SQLite 中的生成列目前還存在一些限制：

生成列不能指定默認值（DEFAULT）。生成列的值總是由 AS 關鍵字后的表達式?jīng)Q定。
生成列不能作為 PRIMARY KEY 的一部分。將來的 SQLite 可能會支持基于 STORED 生成列的主鍵。
生成列的表達式只能引用常量字面值和其他字段，或者確定性的標量函數(shù)。表達式中不能使用子查詢、聚合函數(shù)、窗口函數(shù)或者表函數(shù)。
生成列的表達式可以引用其他的生成列，但是不能引用直接或者間接依賴于它自身的其他生成列。
生成列的表達式不能直接引用 ROWID，但是可以引用 INTEGER PRIMARY KEY 列，雖然兩者效果相同。
每個表至少需要包含一個非生成的普通列。
ALTER TABLE ADD COLUMN 語句不支持 STORED 生成列，但是可以添加 VIRTUAL 生成列。
生成列的數(shù)據(jù)類型和字符排序順序由字段定義中的數(shù)據(jù)類型和 COLLATE 子句決定，與 GENERATED
ALWAYS AS 表達式的數(shù)據(jù)類型和字符排序順序無關。