MySQL :: MySQL 8.4 參考手冊 :: 15.1.20.8 CREATE TABLE 和產生欄位

版本 8.4

8.0 日文

MySQL 8.4 參考手冊 / ... / CREATE TABLE 和產生欄位

15.1.20.8 CREATE TABLE 和產生欄位

CREATE TABLE 支援指定產生欄位。產生欄位的值是根據欄位定義中包含的運算式計算得出的。

NDB 儲存引擎也支援產生欄位。

以下簡單範例顯示一個資料表，該資料表在 sidea 和 sideb 欄位中儲存直角三角形的邊長，並在 sidec 中計算斜邊長度 (其他邊的平方和的平方根)

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);

從資料表選取會產生此結果

mysql> SELECT * FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5 |
|     6 |     8 |                 10 |
+-------+-------+--------------------+

使用 triangle 資料表的任何應用程式都可以存取斜邊值，而無需指定計算這些值的運算式。

產生欄位的定義具有此語法

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

AS (expr) 表示欄位是產生的，並定義用於計算欄位值的運算式。 AS 前面可以加上 GENERATED ALWAYS，讓欄位的產生性質更明確。稍後會討論運算式中允許或禁止的結構。

關鍵字 VIRTUAL 或 STORED 指示欄位值的儲存方式，這會影響欄位的使用。

VIRTUAL：欄位值不會儲存，而是在讀取資料列時評估，緊接在任何 BEFORE 觸發程序之後。虛擬欄位不佔用儲存空間。
InnoDB 支援在虛擬欄位上建立次要索引。請參閱第 15.1.20.9 節：「次要索引和產生欄位」。
STORED：欄位值在插入或更新資料列時評估並儲存。已儲存欄位確實需要儲存空間，而且可以建立索引。

如果未指定任何關鍵字，則預設值為 VIRTUAL。

允許在表格內混合使用 VIRTUAL 和 STORED 欄位。

可以給予其他屬性，以指示欄位是否建立索引、是否可為 NULL，或提供註解。

產生的欄位運算式必須遵守以下規則。如果運算式包含不允許的結構，則會發生錯誤。

允許使用常值、確定性的內建函數和運算子。如果給定表格中的相同資料，多次調用函數會產生相同結果，而與連接的使用者無關，則該函數是確定性的。屬於非確定性且不符合此定義的函數範例：CONNECTION_ID()、CURRENT_USER()、NOW()。
不允許使用儲存函數和可載入函數。
不允許使用儲存程序和函數參數。
不允許使用變數（系統變數、使用者定義變數和儲存程式的本機變數）。
不允許使用子查詢。
產生的欄位定義可以參考其他產生的欄位，但只能參考在表格定義中較早出現的欄位。產生的欄位定義可以參考表格中的任何基本（非產生）欄位，無論其定義較早或較晚出現。
無法在產生的欄位定義中使用 AUTO_INCREMENT 屬性。
AUTO_INCREMENT 欄位無法在產生的欄位定義中當作基本欄位使用。
如果運算式評估導致截斷或提供不正確的函數輸入，CREATE TABLE 陳述式將會終止並出現錯誤，而且 DDL 作業將會被拒絕。

如果運算式評估的資料類型與宣告的欄位類型不同，則會根據 MySQL 常規類型轉換規則，隱含強制轉換為宣告的類型。請參閱第 14.3 節：「運算式評估中的類型轉換」。

如果產生的欄位使用 TIMESTAMP 資料類型，則會忽略 explicit_defaults_for_timestamp 的設定。在這種情況下，如果此變數已停用，則 NULL 不會轉換為 CURRENT_TIMESTAMP。如果該欄位也宣告為 NOT NULL，則嘗試插入 NULL 將會明確地被拒絕，並顯示 ER_BAD_NULL_ERROR。

注意

運算式評估會使用評估時生效的 SQL 模式。如果運算式的任何元件取決於 SQL 模式，除非在所有使用期間 SQL 模式都相同，否則表格的不同使用方式可能會產生不同的結果。

對於 CREATE TABLE ... LIKE，目的地表格會保留原始表格的產生欄位資訊。

對於 CREATE TABLE ... SELECT，目的地表格不會保留所選取表格中的欄位是否為產生欄位的資訊。SELECT 陳述式的部分無法將值指派給目的地表格中的產生欄位。

允許依照產生欄位進行分割。請參閱表格分割。

已儲存產生欄位上的外部索引鍵條件約束無法使用 CASCADE、SET NULL 或 SET DEFAULT 作為 ON UPDATE 參考動作，也無法使用 SET NULL 或 SET DEFAULT 作為 ON DELETE 參考動作。

已儲存產生欄位基本欄位上的外部索引鍵條件約束無法使用 CASCADE、SET NULL 或 SET DEFAULT 作為 ON UPDATE 或 ON DELETE 參考動作。

外部索引鍵條件約束無法參考虛擬產生的欄位。

觸發程序無法使用 NEW.col_name 或 OLD.col_name 來參考產生的欄位。

對於 INSERT、REPLACE 和 UPDATE，如果明確插入、取代或更新產生的欄位，則唯一允許的值為 DEFAULT。

檢視中的產生欄位被認為是可更新的，因為可以將值指派給它。不過，如果明確更新此類欄位，則唯一允許的值為 DEFAULT。

產生欄位有數個使用案例，例如這些

虛擬產生欄位可以用作簡化和統一查詢的方法。複雜的條件可以定義為產生欄位，並從表格中的多個查詢中引用，以確保所有查詢都使用完全相同的條件。
已儲存產生欄位可以用作複雜條件的具體化快取，這些條件在執行時計算成本很高。
產生欄位可以模擬功能索引：使用產生欄位來定義功能運算式並建立索引。這對於處理無法直接建立索引的類型欄（例如 JSON 欄）很有用；如需詳細範例，請參閱為 JSON 欄位索引提供索引產生欄位。
對於已儲存產生欄位，此方法的缺點是值會儲存兩次：一次作為產生欄位的值，另一次則儲存在索引中。
如果產生欄位已建立索引，則最佳化工具會辨識符合欄位定義的查詢運算式，並在查詢執行期間視情況使用欄位的索引，即使查詢未直接依名稱參考欄位也一樣。如需詳細資訊，請參閱第 10.3.11 節：「最佳化工具使用產生欄位索引」。

範例

假設表格 t1 包含 first_name 和 last_name 欄位，而且應用程式經常使用如下的運算式來建構完整名稱

SELECT CONCAT(first_name,' ',last_name) AS full_name FROM t1;

避免寫出運算式的一種方法是在 t1 上建立檢視 v1，這可以讓應用程式直接選取 full_name，而無需使用運算式，從而簡化應用程式

CREATE VIEW v1 AS
SELECT *, CONCAT(first_name,' ',last_name) AS full_name FROM t1;

SELECT full_name FROM v1;

產生欄位也允許應用程式直接選取 full_name，而無需定義檢視

CREATE TABLE t1 (
  first_name VARCHAR(10),
  last_name VARCHAR(10),
  full_name VARCHAR(255) AS (CONCAT(first_name,' ',last_name))
);

SELECT full_name FROM t1;