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

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 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;