15.1.27 CREATE VIEW Statement

15.1.27 CREATE VIEW Statement

1CREATE
2    [OR REPLACE]
3    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
4    [MATERIALIZED] ...
5    [DEFINER = user]
6    [SQL SECURITY { DEFINER | INVOKER }]
7    VIEW view_name [(column_list)]
8    AS select_statement
9    [WITH [CASCADED | LOCAL] CHECK OPTION]
10
11CREATE
12    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
13    [DEFINER = user]
14    [SQL SECURITY { DEFINER | INVOKER }]
15    [IF NOT EXISTS] VIEW view_name [(column_list)]
16    AS select_statement
17    [WITH [CASCADED | LOCAL] CHECK OPTION]

CREATE VIEW statement는 새로운 view를 생성하거나, OR REPLACE 절이 주어졌을 경우 기존 view를 대체합니다. view가 존재하지 않는다면, CREATE OR REPLACE VIEW는 CREATE VIEW와 동일합니다. view가 이미 존재한다면, CREATE OR REPLACE VIEW는 그것을 대체합니다.

Materialized view는 MySQL HeatWave에서만 지원됩니다. 자세한 내용은 Query Materalized Views를 참조하십시오.

_select_statement_는 view의 정의를 제공하는 SELECT statement입니다. (view에서 선택하는 것은 사실상 SELECT statement를 사용하는 것과 같습니다.) _select_statement_는 기본 테이블 또는 다른 view에서 선택할 수 있습니다. SELECT statement는 VALUES statement를 소스로 사용할 수 있거나, CREATE TABLE ... SELECT의 경우처럼 TABLE statement로 대체될 수 있습니다.

IF NOT EXISTS는 view가 아직 존재하지 않을 경우에만 view가 생성되도록 합니다. view가 이미 존재하고 IF NOT EXISTS가 지정된 경우, 이 statement는 에러 대신 경고와 함께 성공합니다. 이 경우 view 정의는 변경되지 않습니다. 예를 들면 다음과 같습니다:

1mysql> CREATE VIEW v1 AS SELECT c1, c3 FROM t1;
2Query OK, 0 rows affected (0.01 sec)
3
4mysql> CREATE VIEW v1 AS SELECT c1, c3 FROM t1;
5ERROR 1050 (42S01): Table 'v1' already exists
6mysql> CREATE VIEW IF NOT EXISTS v1 AS SELECT c1, c3 FROM t1;
7Query OK, 0 rows affected, 1 warning (0.01 sec)
8
9mysql> SHOW WARNINGS;
10+-------+------+---------------------------+
11| Level | Code | Message                   |
12+-------+------+---------------------------+
13| Note  | 1050 | Table 'v1' already exists |
14+-------+------+---------------------------+
151 row in set (0.00 sec)
16
17mysql> SHOW CREATE VIEW v1\G
18*************************** 1. row ***************************
19                View: v1
20         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`vuser`@`localhost` SQL
21SECURITY DEFINER VIEW `v1` AS select `t1`.`c1` AS `c1`,`t1`.`c3` AS `c3` from `t1`
22character_set_client: utf8mb4
23collation_connection: utf8mb4_0900_ai_ci
241 row in set (0.00 sec)

IF NOT EXISTS와 OR REPLACE는 상호 배타적이며 동일한 CREATE VIEW statement에서 함께 사용할 수 없습니다. 그렇게 시도하면 구문 오류와 함께 statement가 거부됩니다.

view 사용에 대한 제약 사항은 Section 27.11, “Restrictions on Views”를 참조하십시오.

view 정의는 생성 시점에 “고정(frozen)”되며, 이후에 기본 테이블 정의가 변경되더라도 영향을 받지 않습니다. 예를 들어, 어떤 view가 테이블에 대해 SELECT *로 정의된 경우, 나중에 테이블에 추가되는 새로운 컬럼은 view의 일부가 되지 않으며, 테이블에서 컬럼을 drop하면 view에서 select할 때 에러가 발생합니다.

ALGORITHM 절은 MySQL이 view를 처리하는 방식에 영향을 줍니다. DEFINER 및 SQL SECURITY 절은 view가 호출될 때 접근 권한을 검사하는 데 사용되는 보안 컨텍스트를 지정합니다. WITH CHECK OPTION 절은 view가 참조하는 테이블의 행에 대한 insert 또는 update를 제한하기 위해 사용할 수 있습니다. 이 절들에 대해서는 이 section의 뒷부분에서 설명합니다.

CREATE VIEW statement를 사용하려면 view에 대한 CREATE VIEW 권한과, SELECT statement에서 선택되는 각 컬럼에 대한 어떤 권한이 필요합니다. SELECT statement의 다른 곳에서 사용되는 컬럼에 대해서는 SELECT 권한이 필요합니다. OR REPLACE 절이 존재하는 경우, view에 대한 DROP 권한도 있어야 합니다. DEFINER 절이 존재하는 경우, 필요한 권한은 Section 27.8, “Stored Object Access Control”에서 설명하는 것처럼 user 값에 따라 달라집니다.

view가 참조될 때, 권한 검사는 이 section의 뒷부분에서 설명하는 방식으로 수행됩니다.

view는 데이터베이스에 속합니다. 기본적으로, 새로운 view는 기본 데이터베이스에 생성됩니다. 특정 데이터베이스에 view를 명시적으로 생성하려면, view 이름을 데이터베이스 이름으로 한정하기 위해 db_name.view_name 구문을 사용합니다:

1CREATE VIEW test.v AS SELECT * FROM t;

SELECT statement 내의 한정되지 않은 테이블 또는 view 이름은 기본 데이터베이스를 기준으로 해석됩니다. view는 적절한 데이터베이스 이름으로 테이블 또는 view 이름을 한정하여 다른 데이터베이스의 테이블이나 view를 참조할 수 있습니다.

하나의 데이터베이스 내에서 기본 테이블과 view는 동일한 네임스페이스를 공유하므로, 기본 테이블과 view는 동일한 이름을 가질 수 없습니다.

SELECT statement에 의해 검색되는 컬럼은 테이블 컬럼에 대한 단순 참조일 수도 있고, 함수, 상수 값, 연산자 등을 사용하는 표현식일 수도 있습니다.

view는 기본 테이블과 마찬가지로 중복되지 않는 고유한 컬럼 이름을 가져야 합니다. 기본적으로, SELECT statement에 의해 검색되는 컬럼 이름이 view 컬럼 이름으로 사용됩니다. view 컬럼의 이름을 명시적으로 정의하려면, 쉼표로 구분된 식별자 목록으로 이루어진 선택적 column_list 절을 지정합니다. _column_list_에 있는 이름의 개수는 SELECT statement에 의해 검색되는 컬럼의 개수와 같아야 합니다.

view는 다양한 종류의 SELECT statement에서 생성될 수 있습니다. 기본 테이블 또는 다른 view를 참조할 수 있습니다. 조인, UNION, 서브쿼리를 사용할 수 있습니다. SELECT는 테이블을 전혀 참조하지 않아도 됩니다:

1CREATE VIEW v_today (today) AS SELECT CURRENT_DATE;

다음 예시는 다른 테이블의 두 컬럼과 그 컬럼으로부터 계산된 표현식을 선택하는 view를 정의합니다:

1mysql> CREATE TABLE t (qty INT, price INT);
2mysql> INSERT INTO t VALUES(3, 50);
3mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
4mysql> SELECT * FROM v;
5+------+-------+-------+
6| qty  | price | value |
7+------+-------+-------+
8|    3 |    50 |   150 |
9+------+-------+-------+

view 정의에는 다음과 같은 제약이 적용됩니다:

• SELECT statement는 시스템 변수 또는 사용자 정의 변수를 참조할 수 없습니다.

• 저장 프로그램 내에서, SELECT statement는 프로그램 매개변수 또는 로컬 변수를 참조할 수 없습니다.

• SELECT statement는 준비된 statement 매개변수를 참조할 수 없습니다.

• 정의에서 참조되는 테이블 또는 view는 모두 존재해야 합니다. view가 생성된 이후에, 정의에서 참조하는 테이블 또는 view가 drop되면, view를 사용하는 것은 에러를 발생시킵니다. 이와 같은 문제에 대해 view 정의를 검사하려면, CHECK TABLE statement를 사용합니다.

• 정의는 TEMPORARY 테이블을 참조할 수 없으며, TEMPORARY view를 생성할 수도 없습니다.

• view에는 트리거를 연결할 수 없습니다.

• SELECT statement에서 컬럼 이름에 대한 alias는 최대 컬럼 길이인 64자(최대 alias 길이 256자가 아님)를 기준으로 검사됩니다.

ORDER BY는 view 정의에서 허용되지만, view를 선택하는 statement에 자체적인 ORDER BY가 있는 경우 무시됩니다.

정의에 있는 다른 옵션 또는 절은 view를 참조하는 statement의 옵션 또는 절에 추가되지만, 그 효과는 정의되어 있지 않습니다. 예를 들어, view 정의에 LIMIT 절이 포함되어 있고, view를 선택하는 statement에도 자체적인 LIMIT 절이 있을 경우, 어떤 limit이 적용되는지는 정의되어 있지 않습니다. 동일한 원칙은 SELECT 키워드 뒤에 오는 ALL, DISTINCT, SQL_SMALL_RESULT와 같은 옵션 및 INTO, FOR UPDATE, FOR SHARE, LOCK IN SHARE MODE, PROCEDURE와 같은 절에도 적용됩니다.

시스템 변수를 변경하여 쿼리 처리 환경을 변경하면 view에서 얻는 결과에 영향을 줄 수 있습니다:

1mysql> CREATE VIEW v (mycol) AS SELECT 'abc';
2Query OK, 0 rows affected (0.01 sec)
3
4mysql> SET sql_mode = '';
5Query OK, 0 rows affected (0.00 sec)
6
7mysql> SELECT "mycol" FROM v;
8+-------+
9| mycol |
10+-------+
11| mycol |
12+-------+
131 row in set (0.01 sec)
14
15mysql> SET sql_mode = 'ANSI_QUOTES';
16Query OK, 0 rows affected (0.00 sec)
17
18mysql> SELECT "mycol" FROM v;
19+-------+
20| mycol |
21+-------+
22| abc   |
23+-------+
241 row in set (0.00 sec)

DEFINER 및 SQL SECURITY 절은 view를 참조하는 statement가 실행될 때 view에 대한 접근 권한을 검사할 때 사용할 MySQL 계정을 결정합니다. 유효한 SQL SECURITY characteristic 값은 DEFINER(기본값)와 INVOKER입니다. 이 값들은 필요한 권한이 각각 view를 정의한 사용자 또는 view를 호출한 사용자가 보유해야 함을 나타냅니다.

DEFINER 절이 존재하는 경우, user 값은 'user_name'@'host_name', CURRENT_USER, 또는 CURRENT_USER()로 지정되는 MySQL 계정이어야 합니다. 허용되는 user 값은 Section 27.8, “Stored Object Access Control”에서 설명하는 것처럼 사용자가 보유한 권한에 따라 달라집니다. view 보안에 대한 추가 정보도 해당 section을 참조하십시오.

DEFINER 절이 생략되면, 기본 definer는 CREATE VIEW statement를 실행하는 사용자입니다. 이는 DEFINER = CURRENT_USER를 명시적으로 지정하는 것과 동일합니다.

view 정의 내에서, CURRENT_USER 함수는 기본적으로 view의 DEFINER 값을 반환합니다. SQL SECURITY INVOKER characteristic으로 정의된 view의 경우, CURRENT_USER는 view의 invoker 계정을 반환합니다. view 내에서의 사용자 감사(auditing)에 대한 정보는 Section 8.2.23, “SQL-Based Account Activity Auditing”를 참조하십시오.

SQL SECURITY DEFINER characteristic으로 정의된 저장 루틴 내에서는, CURRENT_USER가 루틴의 DEFINER 값을 반환합니다. 루틴 내에서 정의된 view의 정의에 DEFINER 값으로 CURRENT_USER가 포함된 경우, 이 값 역시 영향을 받습니다.

MySQL은 다음과 같이 view 권한을 검사합니다:

• view 정의 시점에, view 생성자는 view에서 접근하는 최상위 객체를 사용하는 데 필요한 권한을 보유해야 합니다. 예를 들어, view 정의가 테이블 컬럼을 참조하는 경우, 생성자는 정의의 select 리스트에 있는 각 컬럼에 대해 어떤 권한이든 보유해야 하며, 정의의 다른 곳에서 사용되는 각 컬럼에 대해서는 SELECT 권한을 보유해야 합니다. 정의가 저장 함수(stored function)를 참조하는 경우, 함수 호출 시점에 필요한 권한만 검사할 수 있습니다. 함수 호출 시점에 필요한 권한은 함수가 실행될 때만 검사할 수 있습니다. 호출마다 함수 내에서 서로 다른 실행 경로가 선택될 수 있기 때문입니다.

• view를 참조하는 사용자는 view에 접근하기 위한 적절한 권한을 가지고 있어야 합니다(예: view에서 select하려면 SELECT, view에 insert하려면 INSERT 등).

• view가 참조되면, view에서 접근하는 객체에 대한 권한은 view의 SQL SECURITY characteristic이 각각 DEFINER 또는 INVOKER인지에 따라 view DEFINER 계정 또는 invoker가 보유한 권한을 기준으로 검사됩니다.

• view 참조가 저장 함수의 실행을 유발하는 경우, 함수 내에서 실행되는 statement에 대한 권한 검사는 함수의 SQL SECURITY characteristic이 DEFINER인지 INVOKER인지에 따라 달라집니다. 보안 characteristic이 DEFINER이면, 함수는 DEFINER 계정의 권한으로 실행됩니다. characteristic이 INVOKER이면, 함수는 view의 SQL SECURITY characteristic에 의해 결정되는 권한으로 실행됩니다.

예: view가 저장 함수에 의존하고, 그 함수가 다른 저장 루틴을 호출할 수 있습니다. 예를 들어, 다음 view는 저장 함수 f()를 호출합니다:

1CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name);

f()에 다음과 같은 statement가 포함되어 있다고 가정합니다:

1IF name IS NULL then
2  CALL p1();
3ELSE
4  CALL p2();
5END IF;

f() 내에서 실행되는 statement에 필요한 권한은 f()가 실행될 때 검사되어야 합니다. 이는 f() 내의 실행 경로에 따라 p1() 또는 p2()에 대한 권한이 필요할 수 있음을 의미합니다. 이러한 권한은 런타임에 검사되어야 하며, 권한을 보유해야 하는 사용자는 view v와 함수 f()의 SQL SECURITY 값에 의해 결정됩니다.

view에 대한 DEFINER 및 SQL SECURITY 절은 표준 SQL에 대한 확장입니다. 표준 SQL에서 view는 SQL SECURITY DEFINER 규칙을 사용하여 처리됩니다. 표준에서는 view의 definer(이는 view 스키마의 소유자와 동일합니다)가 view에 대해 적용 가능한 권한(예: SELECT)를 얻고, 이를 grant할 수 있다고 명시합니다. MySQL에는 스키마 “owner”라는 개념이 없기 때문에, MySQL은 definer를 식별하는 절을 추가합니다. DEFINER 절은 표준이 갖고자 하는 것, 즉 view를 누가 정의했는지에 대한 영구적인 기록을 제공하고자 하는 의도로 도입된 확장입니다. 이것이 기본 DEFINER 값이 view 생성자의 계정인 이유입니다.

선택적 ALGORITHM 절은 표준 SQL에 대한 MySQL 확장입니다. 이는 MySQL이 view를 처리하는 방식에 영향을 줍니다. ALGORITHM은 MERGE, TEMPTABLE, UNDEFINED 세 가지 값을 가질 수 있습니다. 자세한 내용은 Section 27.6.2, “View Processing Algorithms”과 Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”를 참조하십시오.

일부 view는 updatable입니다. 즉, 이러한 view는 기본 테이블의 내용을 갱신하기 위해 UPDATE, DELETE, INSERT와 같은 statement에서 사용할 수 있습니다. view가 updatable이 되려면, view의 행과 기본 테이블의 행 사이에 1:1 관계가 있어야 합니다. 또한 view를 nonupdatable로 만드는 몇 가지 다른 구성 요소도 있습니다.

view에서 generated 컬럼은 값을 할당할 수 있기 때문에 updatable로 간주됩니다. 그러나 이러한 컬럼을 명시적으로 update하는 경우 허용되는 값은 DEFAULT뿐입니다. generated 컬럼에 대한 정보는 Section 15.1.24.8, “CREATE TABLE and Generated Columns”을 참조하십시오.

WITH CHECK OPTION 절은 updatable view에서 사용할 수 있으며, _select_statement_의 WHERE 절이 true인 행에 대해서만 insert 또는 update가 허용되도록 합니다.

updatable view에 대한 WITH CHECK OPTION 절에서, LOCAL 및 CASCADED 키워드는 view가 다른 view를 기반으로 정의된 경우 check 검사 범위를 결정합니다. LOCAL 키워드는 CHECK OPTION을 정의 중인 view로만 제한합니다. CASCADED는 기본 view에 대한 검사도 함께 수행되도록 합니다. 두 키워드 모두 지정되지 않은 경우 기본값은 CASCADED입니다.

updatable view 및 WITH CHECK OPTION 절에 대한 자세한 내용은 Section 27.6.3, “Updatable and Insertable Views”와 Section 27.6.4, “The View WITH CHECK OPTION Clause”를 참조하십시오.