MySQL :: MySQL 9.0 參考手冊 :: 27.6.3 可更新和可插入的視圖

版本 9.0

8.4 目前
8.0
5.7

8.0 日語

MySQL 9.0 參考手冊 / ... / 可更新和可插入的視圖

27.6.3 可更新和可插入的視圖

有些視圖是可更新的，對它們的參照可以用來指定要在資料變更陳述式中更新的表格。也就是說，您可以在 UPDATE、DELETE 或 INSERT 等陳述式中使用它們，以更新基礎表格的內容。衍生表格和通用表格運算式也可以在多表格 UPDATE 和 DELETE 陳述式中指定，但只能用於讀取資料以指定要更新或刪除的列。一般來說，視圖參照必須是可更新的，這表示它們可能會被合併，而不會被具體化。複合視圖有更複雜的規則。

若要使視圖可更新，視圖中的列與基礎表格中的列之間必須存在一對一的關係。還有其他一些結構會使視圖不可更新。更具體來說，如果視圖包含下列任何項目，則該視圖不可更新

彙總函數或視窗函數 (SUM()、MIN()、MAX()、COUNT() 等等)
DISTINCT
GROUP BY
HAVING
UNION 或 UNION ALL
選取清單中的子查詢
選取清單中不相依的子查詢對於 INSERT 會失敗，但對於 UPDATE、DELETE 則是沒問題的。對於選取清單中相依的子查詢，不允許任何資料變更陳述式。
某些聯結 (請參閱本節稍後的其他聯結討論)
FROM 子句中參照不可更新的視圖
WHERE 子句中的子查詢參照 FROM 子句中的表格
僅參照常值 (在此情況下，沒有基礎表格可更新)
ALGORITHM = TEMPTABLE (使用暫時表格始終會使視圖不可更新)
多次參照基本表格的任何列 (對於 INSERT 會失敗，對於 UPDATE、DELETE 則沒問題)

視圖中的產生欄會被視為可更新，因為可以為其指派值。但是，如果明確更新此類欄，則唯一允許的值是 DEFAULT。如需產生欄的相關資訊，請參閱第 15.1.20.8 節，「CREATE TABLE 和產生欄」。

如果多表格視圖可以使用 MERGE 演算法處理，則它有時可能會是可更新的。若要使其運作，視圖必須使用內部聯結 (而不是外部聯結或 UNION)。此外，只能更新視圖定義中的單一表格，因此 SET 子句必須僅命名視圖中其中一個表格的欄。即使從理論上來說可以使用 UNION ALL，但使用 UNION ALL 的視圖不允許使用。

關於可插入性 (使用 INSERT 陳述式可更新)，如果可更新的視圖也滿足視圖欄的以下其他需求，則該視圖可插入

視圖欄名稱不得重複。
視圖必須包含基本表格中所有沒有預設值的欄。
視圖欄必須是簡單的欄參照。它們不得為運算式，例如這些
```
3.14159
col1 + 3
UPPER(col2)
col3 / col4
(subquery)
```

MySQL 在 CREATE VIEW 時會設定一個稱為視圖可更新性旗標的旗標。如果 UPDATE 和 DELETE (以及類似的操作) 對於視圖是合法的，則該旗標會設定為 YES (true)。否則，該旗標會設定為 NO (false)。Information Schema VIEWS 表格中的 IS_UPDATABLE 欄會顯示此旗標的狀態。這表示伺服器始終知道視圖是否可更新。

如果視圖不可更新，則 UPDATE、DELETE 和 INSERT 等陳述式是不合法的，並且會遭到拒絕。(即使視圖可更新，也可能無法將資料插入其中，如本節其他地方所述。)

視圖的可更新性可能會受到 updatable_views_with_limit 系統變數值的影響。請參閱第 7.1.8 節，「伺服器系統變數」。

對於以下討論，假設存在這些表格和視圖

CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (c INTEGER);
CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1;
CREATE VIEW vup AS SELECT * FROM t2;
CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c;

允許以下 INSERT、UPDATE 和 DELETE 陳述式

INSERT：INSERT 陳述式的插入表格可以是合併的視圖參照。如果視圖是聯結視圖，則視圖的所有元件都必須是可更新的 (而不是具體化的)。對於多表格可更新視圖，如果將資料插入單一表格，則 INSERT 可以運作。
此陳述式無效，因為聯結視圖的一個元件不可更新
```
INSERT INTO vjoin (c) VALUES (1);
```
此陳述式有效；視圖不包含任何具體化的元件
```
INSERT INTO vup (c) VALUES (1);
```
UPDATE：UPDATE 陳述式中要更新的表格可以是合併的視圖參照。如果視圖是聯結視圖，則視圖的至少一個元件必須是可更新的 (這與 INSERT 不同)。
在多表格 UPDATE 語句中，該語句中被更新的表格參照必須是基礎表格或可更新的視圖參照。未被更新的表格參照可以是實體化視圖或衍生表格。
此語句有效；欄位 c 來自於連接視圖中可更新的部分
```
UPDATE vjoin SET c=c+1;
```
此語句無效；欄位 x 來自於不可更新的部分
```
UPDATE vjoin SET x=x+1;
```
此語句有效；多表格 UPDATE 語句中被更新的表格參照是一個可更新的視圖 (vup)
```
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET c=c+1;
```
此語句無效；它試圖更新一個實體化的衍生表格
```
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET s=s+1;
```
DELETE：在 DELETE 語句中，要刪除的表格必須是合併視圖。不允許使用連接視圖（這與 INSERT 和 UPDATE 不同）。
此語句無效，因為該視圖是一個連接視圖
```
DELETE vjoin WHERE ...;
```
此語句有效，因為該視圖是一個合併（可更新）視圖
```
DELETE vup WHERE ...;
```
此語句有效，因為它是從一個合併（可更新）視圖中刪除
```
DELETE vup FROM vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...;
```

接下來會有更進一步的討論和範例。

本節稍早的討論指出，如果不是所有欄位都是簡單的欄位參照（例如，如果它包含的是表達式或複合表達式的欄位），則視圖不可插入。儘管這樣的視圖不可插入，但如果您僅更新不是表達式的欄位，它仍可更新。考慮以下視圖

CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;

此視圖不可插入，因為 col2 是一個表達式。但是，如果更新沒有嘗試更新 col2，則它是可更新的。此更新是允許的

UPDATE v SET col1 = 0;

此更新是不允許的，因為它嘗試更新一個表達式欄位

UPDATE v SET col2 = 0;

如果一個表格包含一個 AUTO_INCREMENT 欄位，則插入到該表格上不包含 AUTO_INCREMENT 欄位的可插入視圖中，不會更改 LAST_INSERT_ID() 的值，因為將預設值插入到視圖不包含的欄位的副作用不應可見。

上一步首頁上一層下一步