23.5.3 Updatable and Insertable Views — MySQL 5.7

23.5.3 Обновляемые и вставляемые представления

Некоторые представления обновляемые, и ссылки на них могут быть использованы для указания таблиц, которые должны быть обновлены в операторах изменения данных. То есть, вы можете использовать их в операторах, таких как UPDATE, DELETE или INSERT для обновления содержимого основной таблицы. Производные таблицы также могут быть указаны в операторах работы с несколькими таблицами UPDATE и DELETE, но могут использоваться только для чтения данных для указания строк, которые необходимо обновить или удалить. Как правило, ссылки на представления должны быть обновляемыми, что означает, что они могут быть объединены, а не материализованы. Композитные представления имеют более сложные правила.

Для того, чтобы представление было обновляемым, должно существовать взаимно-однозначное соответствие между строками в представлении и строками в основной таблице. Также существуют определенные другие конструкции, которые делают представление не обновляемым. Более конкретно, представление не обновляемое, если оно содержит следующие элементы:

Агрегатные функции (SUM(), MIN(), MAX(), COUNT() и так далее)
DISTINCT
GROUP BY
HAVING
UNION или UNION ALL
Подзапрос в списке выбора

Перед MySQL 5.7.11, подзапросы в списке выбора завершались ошибкой при INSERT, но работали корректно для UPDATE, DELETE. Начиная с MySQL 5.7.11, это по-прежнему верно для независимых подзапросов. Для зависимых подзапросов в списке выбора разрешены любые операции изменения данных.
Некоторые соединения (см. дополнительное обсуждение соединений позже в этом разделе)
Ссылка на не обновляемое представление в клаузе FROM
Подзапрос в клаузе WHERE, который ссылается на таблицу в клаузе FROM
Ссылается только на литеральные значения (в этом случае нет основной таблицы для обновления)
ALGORITHM = TEMPTABLE (использование временной таблицы всегда делает представление не обновляемым)
Несколько ссылок на любой столбец базовой таблицы (ошибки для INSERT, корректно для UPDATE, DELETE)

Сгенерированный столбец в представлении считается обновляемым, потому что в него можно присвоить значение. Однако, если такой столбец обновляется явно, единственное допустимое значение равно DEFAULT. Подробности о сгенерированных столбцах см. в Разделе 13.1.18.7, «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, являются недопустимыми и отклоняются. (Даже если представление обновляемое, в него может быть невозможно вставить данные, как описано в другом месте в этом разделе.)

Флаг IS_UPDATABLE может быть ненадежным, если представление зависит от одного или нескольких других представлений, и одно из этих базовых представлений обновляется. Независимо от значения IS_UPDATABLE, сервер отслеживает обновляемость представления и правильно отклоняет операции изменения данных для представлений, которые не обновляемы. Если значение IS_UPDATABLE для представления стало неточным из-за изменений в базовых представлениях, его можно обновить, удалив и снова создав представление.

Обновляемость представлений может быть повлияна значением системной переменной updatable_views_with_limit. См. Раздел 5.1.7, «Системные переменные сервера».

Для дальнейшего обсуждения предположим, что эти таблицы и представления существуют:

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).

В операторе обновления с несколькими таблицами ссылки на обновляемые таблицы оператора должны быть базовыми таблицами или обновляемыми ссылками на представления. Ссылки на неу обновляемые таблицы могут быть материализованными представлениями или производными таблицами.

Этот оператор действителен; столбец c из обновляемой части представления соединения:
```
UPDATE vjoin SET c=c+1;
```
Этот оператор недействителен; столбец x из не обновляемой части:
```
UPDATE vjoin SET x=x+1;
```
Этот оператор действителен; ссылка на обновляемую таблицу в операторе обновления с несколькими таблицами является обновляемым представлением (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 ...;
```

Дополнительные обсуждения и примеры следуют.

END_OF_DOCUMENT_MARKER

Ранее в этом разделе отмечалось, что представление не может быть вставлено, если все столбцы не являются простыми ссылками на столбцы (например, если оно содержит столбцы, которые являются выражениями или составными выражениями). Хотя такое представление не может быть вставлено, оно может быть обновляемым, если вы обновляете только столбцы, которые не являются выражениями. Рассмотрим это представление:

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