Additional languages
« 4.1 ALTER VIEW 構文
4.3 DROP VIEW 構文 »
Section Navigation      [Toggle]

4.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 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を使用している画面は、理論的には更新可能であるかもしれないが、インプリメンテーションがこれらを処理するためにテンポラリーテーブルを使うので拒絶されます。

更新可能なマルチテーブル画面の場合、それをシングルテーブルに挿入すると、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 システム変数の値によって影響を被ります｡詳しくはSystem Variablesを参照してください。