MySQL view(뷰)

MySQL view(뷰)

2020. 2. 17. 17:26ㆍMySQL

뷰의 작동 방식은 다음과 같다. 뷰를 생성하여 SELECT문으로 데이터를 조회하면 테이블과 동일한 모양으로 확인할 수 있다.

뷰를 사용하는 이유는 다음과 같은 장점이 있기 때문이다.

보안에 도움이 된다:

위와 같은 뷰에는 사용자의 이름과 주소만 있을 뿐, 시용자의 중요한 개인정보인 출생년도, 연락처, 키, 가입일 등은 들어 있지 않다. 예를 들어 다른 사용자에게 무언가 작업을 맡겼을 때 연락처 등의 중요한 정보에는 접근하지 않고 이름과 주소 데이터만 가지고 작업하도록 할 때 뷰를 사용할 수 있다.

복잡한 쿼리를 단순화할 수 있다:

이렇게 줄도 길고 여러 조건이 들어간 쿼리를 자주 사용하게 될 경우 이 쿼리문을 뷰로 만들어 놓으면 편리하다.

CREATE VIEW Statement
ALTER VIEW Statement
DROP VIEW Statement
Using Views

1. View Syntax

2. View Processing Algorithms

3. Updatable and insertable Views

4. The View WITH CHECK OPTION Clause

5. View Metadata

CREATE VIEW Statement

CREATE VIEW 명령문은 새 뷰를 만든다. OR REPLACE 절을 뒤에 붙이면 존재하고 있던 뷰를 대체한다.

여기서 select_statement는 만들고자 하는 뷰를 정의해주는 SELECT 명령문이다. select_statement는 기존의 테이블이나 다른 뷰들의 SELECT 명령문이 될 수 있다.

뷰는 만든 시간에 멈취있으며, 뷰를 만든 이후에는 뷰의 근본이 되는 테이블을 정의하는 변화에 영향을 받지 않는다. 예를 들어 어떤 테이블의 SELECT *을 사용한 뷰가 있다면, 새 컬럼들이 그 테이블에 나중에 추가되어도 그 뷰에는 추가되지 않는다. 뷰에서 그 컬럼들을 선택하면 에러가 발생한다.

ALGORITHM 절은 MySQL이 뷰를 처리하는 데 영향을 미친다.

DEFINER와 SQL SECURITY 절은 뷰를 만들 때 접근권한을 체크할 때 사용한다.

WITH CHECK OPTION 절은 뷰에 참조된 테이블에 행을 INSERT 또는 UPDATE 하는 것을 제약한다.

(CREATE VIEW 명령문에서는 다른 곳에서 SELECT 명령문이 쓰인 컬럼에 대한 SELECT 우선권을 가진다. OR REPLACE 절이 있으면 뷰에 대한 DROP 우선권도 가진다. )

뷰는 데이터베이스에 속한다. 뷰를 만들면 기본 설정되어 있는 데이터베이스에 생성되는 것이 디폴트이다. 다른 데이터베이스에 뷰를 만들고 싶으면 db_name.view_name 구문을 사용하여 원하는 데이터베이스의 이름을 명시한다.

한 데이터베이스 안에서는 베이스 테이블과 뷰가 같은 namespace를 공유하기 때문에 뷰 이름을 테이블 이름과 동일하게 설정할 수 없다.

뷰는 베이스 테이블과 마찬가지로 중복되지 않는 유니크한 컬럼명을 가진다. AS 뒤의 SELECT문에 사용된 컬럼명이 뷰의 컬럼 이름으로 사용되는 것이 디폴트이다. 베이스 테이블의 컬럼명과 다르게 컬럼명을 설정하려면 VIEW view_name 뒤에 [(column_list)]와 같이 컬럼들의 이름을 지정한다. column_list에는 반드시 SELECT 문의 컬럼 수와 동일한 컬럼을 작성해야 한다.

뷰를 만들 때 SELECT문을 사용하여 테이블이나 다른 뷰를 선택한다고 하기는 했지만 SELECT문 없이 아무 테이블도 지정하지 않을 수도 있다.

CREATE VIEW v_today (today) AS SELECT CURRENT_DATE;

ALTER VIEW Statement

이 명령문은 정의된 뷰를 수정하는 것인데 여기서 뷰는 반드시 존재하는 것이어야 한다. 문법은 CREATE VIEW와 거의 유사하다.

DROP VIEW Statement

(맞게 해석했는지 모르겠지만) DROP 명령문에 존재하지 않는 뷰의 이름을 넣으면 그 뷰를 DROP할 수 없을 뿐만 아니라 존재하고 있는 모든 뷰 역시도 DROP된다는 에러가 발생한다.

If any views named in the argument list do not exist, the statement returns an error indicating by name which nonexisting views it was unable to drop, but also drops all views in the list that do exist.

Using Views

1. View Syntax

CREATE VIEW

ALTER VIEW

DROP VIEW

2. View Processing Algorithms

MySQL의 CREATE VIEW나 ALTER VIEW 와 같은 ALGORITHM절은 표준 SQL에 대한 MySQL의 확장이다. ALGORITHM은 MERGE, TEMPTABLE, UNDEFINED 세 변수를 가진다.

MERGE는 뷰와 뷰 정의문을 참조하는 명령문의 문장은 명령문의 부분과 상응되는 뷰 대체 정의문 부분과 병합된다.

TEMPTABLE은 뷰에서 나온 결과는 임시 테이블 속으로 들어가게 되는데, 이것은 명령문을 실행하기 위해 사용된다.

UNDEFINED는 MySQL은 사용할 알고리즘을 선택한다. 가능하다면 MySQL은 MERGE를 선호하게 되는데, 왜냐하면 MERGE는 일반적으로 보다 효율적이며 또한 뷰는 임시 테이블이 사용되는 경우에는 업데이트를 할 수 없기 때문이다.

확실하게 TEMPTABLE가 선택되는 경우는 임시 테이블이 생성되고 임시 테이블이 명령문 수행을 마치기 위해 사용되기 전에 잠금이 언더라잉(underlying) 테이블에서 릴리즈될 수 있을 경우만 해당된다. 이 결과로 잠금 릴리즈가 MERGE알고리즘 보다 빨리 구현되며, 따라서 뷰를 사용하는 다른 클라이언트가 더 이상 갇히지 않게 된다.

뷰 알고리즘은 세 가지 이유로 UNDEFINED될 수 있다.

CREATE VIEW명령문안에 ALGORITHM구문이 전혀 없다.
CREATE VIEW명령문이 명확한 ALGORITHM = UNDEFINED구문을 가지고 있다.
ALGORITHM = MERGE은 임시 테이블이로 처리되는 뷰에 대해서만 지정된다. 이 경우, MySQL은 경고문을 내 보내고 알고리즘을 UNDEFINED으로 설정한다.

3. Updatable and insertable Views

기본적으로 뷰는 읽기 전용으로 많이 사용되지만 어떤 뷰들은 업데이트가 가능하다. UPDATE, DELETE, INSERT와 같은 명령문에 있는 뷰들을 사용해서 언더라잉 테이블의 내용을 업데이트 할 수가 있다. 또한, 업데이트가 되지 않도록 뷰를 구성할 수도 있다. 보다 특징적으로 하기 위해서, 뷰는 아래의 것 중에 하나를 가지고 있으면 업데이트가 되지 않는다.

Aggregate functions (SUM(), MIN(), MAX(), COUNT(), 등등)
DISTINCT
GROUP BY
HAVING
UNION또는 UNION ALL
선택한 리스트에 있는 서브 쿼리
조인(Join)
FROM구문에 있는 업데이트할 수 없는(non-updatable) 뷰
FROM구문에 있는 테이블을 참조하는 WHERE구문에 있는 서브쿼리
문자 값만을 참조 (이와 같은 경우, 업데이트할 언더라잉 테이블이 없다)
ALGORITHM = TEMPTABLE (임시 테이블 사용은 항상 뷰를 업데이트 불가능 한 것으로 만든다)

삽입이 가능하게 끔 하는 것과 관련해서는 (INSERT명령문을 가지고 업데이트가 될 수 있도록), 업데이트가 가능한 뷰는 뷰 컬럼에 대해 아래의 부가적인 요구 사항이 만족된다면 삽입 가능해(insertable) 진다.

뷰 컬럼 이름이 중복 사용되지 말아야 한다.
뷰는 디폴트 값을 가지고 있지 않는 베이스 테이블에 있는 모든 컬럼을 가지고 있어야 한다.
뷰 컬럼은 단순 컬럼 참조(simple column reference)만 하고 컬럼을 가져오지는(derived column) 않는다. 획득하는 컬럼(derived column)은 수식에서 얻어지는 것이다. (예: 3.14159, col1 + 3, UPPER(col2), col3 / col4, (subquery))

4. The View WITH CHECK OPTION Clause

UPDATE 명령문이 LIMIT 구문을 가지고 있다면, 언더라잉 테이블에서 정의된 primary key에 대한 모든 컬럼을 뷰가 가질 수 없을 때 뷰에 대한 업데이트를 생성할 수 있는지를 결정하는 변수이다.

5. View Metadata

SHOW CREATE VIEW 명령문을 사용하면 뷰의 메타데이터를 확인할 수 있다.