27.5.3 Updatable and Insertable Views — 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. Флаг устанавливается в YES (true), если UPDATE и DELETE (и аналогичные операции) допустимы для представления. В противном случае флаг устанавливается в NO (false). Столбец IS_UPDATABLE в таблице Схемы информации VIEWS отображает состояние этого флага. Это означает, что сервер всегда знает, обновляемо ли представление.

Если представление не обновляемо, операторы, такие как 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(), потому что побочные эффекты вставки значений по умолчанию в столбцы, которые не являются частью представления, не должны быть видны.