MS SQL, Oracleなどではずいぶんと前からVIEWが利用可能でしたが
MYSQLでも5.xからVIEWが利用可能になってます。


CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]


VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

view_sample

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

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

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

view_order_by

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

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

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

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

例)
view_merge

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

* 集約ファンクション (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でなく)内部結合
を使用しなければなりません。また、ビュー定義に含まれている1つのテーブルだけがアップデート可能です。
よって、SET節に基づき、ビュー中の1つのテーブルからカラムだけ選択して名前をつけなくてはなりません。
UNION ALLを使用しているビューは、理論的に更新可能かもしれませんが、実装は処理にテンポラリテーブル
を使用するので拒絶されます。

参考サイト
20.2. CREATE VIEW 構文

Comments are closed.

Post Navigation