MySQL :: MySQL 8.4 參考手冊 :: 27.5.3 可更新與可插入的視圖

版本 8.4

8.0 日語

MySQL 8.4 參考手冊 / ... / 可更新與可插入的視圖

27.5.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 的視圖。

關於可插入性（可使用 INSERT 語句更新），如果視圖的列也滿足以下額外要求，則可更新的視圖是可插入的：

不能有重複的視圖列名稱。
視圖必須包含基礎表格中所有沒有預設值的列。
視圖列必須是簡單的列引用。它們不得是運算式，例如以下運算式：
```
3.14159
col1 + 3
UPPER(col2)
col3 / col4
(subquery)
```

MySQL 在 CREATE VIEW 時設定一個旗標，稱為視圖可更新性旗標。如果 UPDATE 和 DELETE（以及類似的作業）對視圖合法，則該旗標會設為 YES（真）。否則，該旗標會設為 NO（假）。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() 的值，因為將預設值插入到視圖中不包含的欄位的副作用不應該可見。

上一步首頁上一層下一步