MySQL :: MySQL 5.1 リファレンスマニュアル

MySQL 5.1 リファレンスマニュアル :: 20 ビュー :: 20.2 CREATE VIEW 構文

Section Navigation [Toggle]

20.2. `CREATE VIEW` 構文

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

このステートメントは新しいビューを生成させるか、OR REPLACE節を附与すると、既存のビューを他のものと交換します。そのビューが存在しない場合、CREATE OR REPLACE VIEWはCREATE VIEWと同じになります。ビューが存在する場合、CREATE OR REPLACE VIEWはALTER VIEWと同じになります。select_statementはビューの定義を提供するSELECTステートメントです。ステートメントはベーステーブルまたはその他のビューから選択することができます。

このステートメントには、ビューに対するCREATE VIEW権限とSELECTステートメントによって選択された各カラムに対して幾つかの権限が要求されます。SELECTステートメントの中で使用されている他のカラムに対して、SELECT権限を所持していなければなりません。OR REPLACE節が存在している場合、ビューのDROP権限を所持していなければなりません。

ビューはデータベースに付随します。デフォルト設定によって、デフォルトデータベースの中に新しいビューが生成されます。あるデータベース中にビューを明確に生成させるには、ビューを生成するとき、その名称を db_name.view_nameと規定してください。

mysql> CREATE VIEW test.v AS SELECT * FROM t;

ベーステーブルとビューはデータベース中で同じ名称スペースを共有しているので、データベースに同じ名称のベーステーブルとビューを含めることはできません。

ビューには、ベーステーブルと同じように、ユニークなカラム名を重複することなく持たせなければなりません。デフォルト設定に基づき、SELECTステートメントによって復元されたカラムの名称がビューカラム名に対して使用されます。ビューカラムに対して明確な名称を規定するため、オプションのcolumn_list節をコンマで区切りをつけた識別子のリストとして附与することができます。column_listの中に入れる名称の数はSELECTステートメントによって復元されたカラムの数と同じでなければなりません。

SELECT ステートメントによって復元されたカラムはテーブルカラムを引用するシンプルなものにすることができます。これらは関数、定数値、オペレータ等を使用した表現にすることもできます。

SELECTステートメント中の不適切なテーブル名あるいはビュー名はデフォルトデータベースに対して解釈されます。ビューには、適切なデータベース名を使ってそのテーブルまたはビュー名に資格を附与することによって、他のデータベース中のテーブルまたはビューを引用することができます。

ビューは多くの種類のSELECTステートメントから生成することができます。そこから、ベーステーブルまたはビューを参照することができます。ジョイン、UNIONおよびサブ・クエリーを使用することができます。SELECTはテーブルさえ参照する必要がありません。次の例は他のテーブルから２つのカラム並びにそれらのカラムから計算された表現を選択したビューを定義します。

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+

ビュー定義は以下の制限に規定されます。

SELECTステートメントはFROM節の中にサブ・クエリーを含めることができません。
SELECTステートメントはシステム変数もしくはユーザー変数を参照することができません。
SELECTステートメントは準備されたステートメントパラメータを参照することができません。
ストアドルーチン内で、定義はルーチン・パラメータもしくはローカル変数を参照することができません。
定義で参照したテーブルもしくはビューは存在しなければいけません。ただし、ビューを生成し終えた後に、定義が参照するテーブルまたはビューを撤去することができます。この場合、ビューの使用はエラーとなります。この類の問題に対してビュー定義をチェックするには、CHECK TABLEステートメントを使用してください。
定義はTEMPORARYテーブルを参照できない上、TEMPORARYビューを生成させることができません。
ビュー定義内で名称を持つテーブルは存在していなければいけません。
トリガにビューを関連させることはできません。

ORDER BYはビュー定義の中で許容されていますが、それ自身ORDER BYを持つステートメントを使ってビューから選択すると無視されます。

定義中の他のオプションあるいは節に対して、オプションまたはビューを参照するステートメントの節が追加されましたが、その効果は定義されていません。例えば、ビュー定義にLIMIT節が含まれているとき、それ自身のLIMIT節を持つステートメントを使って選択すると、いずれの限界か適用されるかが定義されていません。SELECTキーワードに従うALL、DISTINCTまたはSQL_SMALL_RESULTのようなオプション並びにINTO、FOR UPDATE、LOCK IN SHARE MODEおよびPROCEDUREのような節に関しては、同じ原理が適用します。

ビューを生成させてから、システム変数を変えることによってクエリ処理環境を変えると、ビューから得る結果が影響されることがあります。

mysql> CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));
Query OK, 0 rows affected (0.00 sec)

mysql> SET NAMES 'latin1';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM v;
+-------------------+---------------------+
| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |
+-------------------+---------------------+
| latin1            | latin1_swedish_ci   |
+-------------------+---------------------+
1 row in set (0.00 sec)

mysql> SET NAMES 'utf8';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM v;
+-------------------+---------------------+
| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |
+-------------------+---------------------+
| utf8              | utf8_general_ci     |
+-------------------+---------------------+
1 row in set (0.00 sec)

DEFINER節およびSQL SECURITY節はビューの呼び出しにおいて、アクセス権限をチェックするとき使用すべきセキュリティーコンテキストを規定します。これらはMySQL 5.1.2で追加されています。

CURRENT_USERをCURRENT_USER()として附与することもできます。

SQL SECURITY DEFINER特徴を使って定義されているストアドルーチン内で、CURRENT_USERはルーチン生成者に返します。ビュー定義の中にCURRENT_USERのDEFINER値が含まれている場合、これは、当該ルーチン中で規定されたビューに影響を及ぼします｡

DEFINERのデフォルト値はCREATE VIEWステートメントを実行するユーザです。(これはDEFINER = CURRENT_USERと同じです。)user値を附与する場合、それを「'user_name'@'host_name'フォーマット（GRANTステートメントに使用したと同じフォーマット）の中にあるMySQLアカウントとするべきです。user_name の値とhost_name の値が両方共要求されます。

DEFINER節を規定する場合、SUPER権限を持っていない限り、ユーザの値を除くいかなるアカウントにも値を設定することはできません。これらの規則は有効なDEFINER ユーザ値を決定します。

SUPER権限を持っていない場合、文字によるか、CURRENT_USERを使って規定されているuser値だけが有効なユーザアカウントとなります。デファイナーを他のアカウントに設定することはできません。
SUPER権限を持っている場合、構文的に有効なアカウントネームを規定することができます。そのアカウントが実在しない場合、警告が生成されます。

SQL SECURITY特徴はビューの実行においてビューに対するアクセス権限をチェックする時、どのMySQLアカウントを使用すべきかを決定します。有効な特徴値はDEFINERとINVOKERです。これらはそれぞれ、ビューがそれを定義か起動したユーザによって実行可能でなければならないことを示します。SQL SECURITYのデフォルト値は DEFINERです。

(DEFINER 節とSQL SECURITY節が実装された時)MySQL 5.1.2 以降、ビュー権限はこのようにしてチェックされます。

ビューを定義するとき、ビュー作成者は、ビューがアクセスしたトップレベルのオブジェクトの使用に要する権限を持っていなければなりません。例えば、ビュー定義が保存されているファンクションを参照する時、ファンクションを起動するために必要な権限だけをチェックすればよい場合があります。ファンクションを作動させるに要する権限は、それを実行するときにだけチェックすることができます。ファンクションを呼び出す方法が異なると、ファンクション中にある別な実行経路を使用しなければなない場合があります。
ビューの実行において、SQL SECURITY特性がDEFINERあるいはINVOKERであるか否かによって、ビューがアクセスしたオブジェクトに対する権限が、ビュー生成者あるいはインボーカーが保持する権限と照合してそれぞれチェックされます。
ビューの実行が保存されているファンクションの実行を引き起こす場合、ファンクションの中で実行されたステートメントに対して権限チェックを実行するか否かは、ファンクションがDEFINERあるいはINVOKERのSQL SECURITYファンクションを使用して規定されているか否かによって決まります｡セキュリティー特徴がDEFINERである場合、ファンクションはその生成者の権限を使って作動します。その特徴がINVOKERである場合、ファンクションはビューのSQL SECURITY特徴によって決定された権限を使用して作動します。

(DEFINERおよびSQL SECURITY節が実装される前の)5.1.2以前のMySQLの場合、ビュー中でオブジェクトの使用に要する権限はビュー生成時にチェックされます。

例:ビューは保存されているファンクションに依存し、そのファンクションは他ストアドルーチンを起動する場合があります。例えば、以下のビューは保存されているファンクションf()を起動します。

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

f()にこのようなステートメントが含まれているとします。

IF name IS NULL then
  CALL p1();
ELSE
  CALL p2();
END IF;

f()を実行するとき、f()の中でステートメントの実行に要する権限をチェックする必要があります。これは、f()中の実行経路によって、p1() もしくは p2()に対する権限をチェックする必要があることを意味します。それらの権限はランタイム時にチェックする必要があります。権限を所持していなければないユーザであるか否かはファンクションf()のSQL SECURITY値とビューvによって定義されます。

ビューに対するDEFINER節およびSQL SECURITY節は標準SQLの拡張子です。標準SQLでは、ビューはSQL SECURITY INVOKER.に対する規則を使って処理されます。

5.0.13/5.1.2以前のMySQLで生成されたビューを呼び出す場合、それは、SQL SECURITY DEFINER 節並びにユーザのアカウントと同じDEFINER値を使って生成されたものとして処理されます。しかし、実際のデファイナーが未知なので、MySQLは警告を発行します。警告を撤去するには、ビューを再び生成させて、ビュー定義にDEFINER節を含めれば十分です｡

オプションのALGORITHM節は標準MySQL の拡張子です。ALGORITHMには３つの値が付いています:MERGE、TEMPTABLEまたはUNDEFINED。ALGORITHM節がある場合、デフォルトアルゴリズムはUNDEFINEDとなります。アルゴリズムはMySQL がビューを処理する方法に影響を及ぼします。

MERGEの場合、ビューが参照するステートメントの本文とビュー定義が併合され、ビュー定義の部分が対応するステートメントの部分と取り替えられます。

TEMPTABLEの場合、ビューの結果がテンポラリーテーブルの中に復元され、その後、ステートメントを実行するために使用されます。

UNDEFINEDの場合、MySQLは使用すべきアルゴリズムを選択します。それは出来るだけTEMPTABLEよりMERGEを優先します。これは、MERGEは通常より効率的で、ビューはテンポラリテーブルを使用すると更新可能ではなくなるためです。

明確にTEMPTABLEを選択する理由は、テンポラリテーブルを選んだ後ステートメントの処理終了に使用する前に、内在するテーブルのロックを解放することができるからです。その結果、ロックをMERGEアルゴリズムよりも速やかに解除し、ビューを使う他のクライアントが長時間ブロックされないようにします。

以下に示す３つの理由によって、ビューアルゴリズムをUNDEFINEDにすることができます。

CREATE VIEWステートメントの中にALGORITHM節が現れない。
CREATE VIEWステートメントにALGORITHM = UNDEFINED節が明確に含まれている。
テンポラリテーブルだけを使って処理できるビューに対して、ALGORITHM = MERGEが規定される。この場合、MySQL は警告を発し、アルゴリズムをUNDEFINEDにセットします。

前に述べたように、MERGEは、ビューを参照するステートメントの一部を該当するビュー定義の部分と併合することによってに処理されます。次の例で、MERGEアルゴリズムが作動する方法を簡単に図解説明します。例にこの定義が含まれているビューv_mergeが存在していると見なすと：

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;

例１。我々がこのステートメントを発行すると仮定すると：

SELECT * FROM v_merge;

MySQLはステートメントを以下の通りに処理します：

v_mergeはtとなる
*はvc1, vc2 となり、c1, c2と一致する
ビューWHERE節が追加される

実行すべき結果ステートメントは以下の通りとなります。

SELECT c1, c2 FROM t WHERE c3 > 100;

例2。このステートメントを発行すると仮定します：

SELECT * FROM v_merge WHERE vc1 < 100;

このステートメントは、vc1 < 100がc1 < 100になり、接続詞ANDを使ってビューWHERE節がステートメントWHERE節に追加され(更に、かっこを追加して、その節の部分が前例を正しく使って実行されていることを確かめる)以外、前のステートメントと同様に処理されます。実行すべき結果ステートメントは以下の通りとなります。

SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);

実行されるステートメントは結果的にこの形式の WHERE節を持ちます。

WHERE (select WHERE) AND (view WHERE)

MERGEアルゴリズムは、ビュー中の行と基礎テーブル内の行の間に１対１の関係が要求されます。この関係が保持されない場合、代わりにテンポラリーテーブルを使用しければなりません。ビューに多くの生成子が含まれると、一対一の関係に不足が生じます。

集約ファンクション (SUM()、 MIN()、 MAX()、COUNT()等)
DISTINCT
GROUP BY
HAVING
UNION もしくはUNION ALL
選択リスト中のサブ・クエリ
文字値だけを参照(この場合、基礎となるテーブルは存在しません）

幾つかのビューは更新可能です。すなわち、基礎をなすテーブルの内容を更新するため、UPDATE、DELETEもしくはINSERTのようなステートメントの中でそれらを使うことができます。ビューを更新可能にするため、ビュー中の行と基礎テーブル中の行の間に1対1の関係が存在しなければなりません。ビューを更新不能にするその他の生成子もあります。もっと具体的に言うと、それが以下のいずれかを含んでいるとビューは更新可能となりません。

集約ファンクション(SUM()、 MIN()、 MAX()、COUNT()等)
DISTINCT
GROUP BY
HAVING
UNION もしくはUNION ALL
選択リスト中のサブ・クエリ
特定結合（このセクション中の後の部分に追加した結合の説明参照）
FROM節中の更新不能ビュー
FROM節中のテーブルを参照するWHERE 節中のサブ・クエリ
文字値だけを参照(この場合、更新する基礎となるテーブルは存在しません)
ALGORITHM = TEMPTABLE (テンポラリテーブルの使用は常にビューを更新不能にする)

(INSERTステートメントで更新不能となる)挿入性に関して、それがビューカラムに対するこれらの追加条件も満たすと、更新不能ビューが挿入可能になります。

ビューカラム名に重複があってはなりません。
ビューには、デフォルト値を持っていないベーステーブル内にあるすべてのカラムを含んでいなくてはなりません。
ビューカラムは派生カラムではなく、単純なカラムリファレンスでなければなりません。派生カラムは単純なカラムリファレンスでなく、表現から派生したものです。これらは派生カラムの例です。
```
3.14159
col1 + 3
UPPER(col2)
col3 / col4
(subquery)
```

単純なカラムリファレンスと派生カラムを混合して持つビューは挿入できません。しかし、当該ビューは、派生したものでないこれらのカラムだけをアップデートする場合に限り更新することができます。このビューを想定すると:

CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;

このビューは、col2が表現から派生しているので挿入できません。しかし、col2を更新しようとしていない場合、それはアップデートすることができます。このアップデートは許容されます:

UPDATE v SET col1 = 0;

このアップデートは、それが派生カラムをアップデートしようとしているので許容されません｡

UPDATE v SET col2 = 0;

MERGEアルゴリズムで処理することができると仮定すると、場合によってマルチ・テーブルビューをアップデートすることが可能です。これを実現するには、ビューに(外部結合またはUNIONでなく)内部結合を使用しなければなりません。また、ビュー定義に含まれている１つのテーブルだけがアップデート可能です。よって、SET節に基づき、ビュー中の１つのテーブルからカラムだけ選択して名前をつけなくてはなりません。UNION ALLを使用しているビューは、理論的に更新可能かもしれませんが、実装は処理にテンポラリテーブルを使用するので拒絶されます。

更新可能なマルチテーブルビューの場合、それを1つのテーブルに挿入すると、INSERTを作動させることができます。DELETEはサポートされません。

INSERT DELAYEDはビューでサポートされていません。

テーブルがAUTO_INCREMENTカラムを含んでいないテーブル上にある挿入可能なビューに挿入するAUTO_INCREMENTカラムを含んでいる場合、ビューの一部でないカラムにデフォルト値を挿入した副作用が見えないので、カラムはLAST_INSERT_ID()の値を変更しません。

それに対するselect_statement中のWHERE節が真実であるものを除く行に、更新不能なビューが挿入されるか、当該行が更新されるのを回避するため、WITH CHECK OPTION節を附与することができます。

更新可能なビューに対するWITH CHECK OPTION節に基づき、LOCALとCASCADEDキーワードはビューが他のビューに対して定義される場合、チェックテストの範囲を決めます｡定義されているビューだけに対して、LOCALキーワードはCHECK OPTIONを制限します。CASCADEDは同様に基礎ビューを評価するチェックを起動させます。キーワードが附与されない場合、デフォルト設定はCASCADEDとなります。以下のテーブル並びにビューのセットを考慮すると：

mysql> CREATE TABLE t1 (a INT);
mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
    -> WITH CHECK OPTION;
mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
    -> WITH LOCAL CHECK OPTION;
mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
    -> WITH CASCADED CHECK OPTION;

ここでは、v2ビューとv3ビューが他のビューに対して定義され、v1. v2 にはLOCAL チェックオプションが含まれています。従って、挿入はv2チェックだけに対してテストされます。v3にはCASCADED チェックオプションが含まれているので、挿入は、自身のチェックのみならず、基礎ビューに対してもテストされます。以下のステートメントはこれらの違いを例示したものです。

mysql> INSERT INTO v2 VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'

ビューの更新はupdatable_views_with_limit システム変数の値に影響されます｡項4.2.3. 「システム変数」を参照してください。

Top / Previous / Next / Up / Table of Contents

20.2. CREATE VIEW 構文

20.2. `CREATE VIEW` 構文