27.6.3 Updatable and Insertable Views — MySQL 9.2

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