MySQLのビュー（VIEW）

Habrのコメントでは、表現の使用に関する質問が言及されました。 このトピックは、MySQLバージョン5.0で導入されたビューの概要です。 作成の問題、提出の利点と制限に対処します。

ビューとは何ですか？

ビュー（VIEW）-ビューへのアクセス時にSELECTステートメントを使用して定義されたデータベースへのクエリを実行した結果であるデータベースオブジェクト。



ビューは「仮想テーブル」と呼ばれることもあります。 この名前は、ユーザーがビューをテーブルとして使用できるという事実によるものですが、ビュー自体にはデータは含まれませんが、アクセス時にテーブルからデータを抽出します。 ベーステーブルでデータが変更された場合、ユーザーはこのテーブルを使用してビューにアクセスするときに関連データを受け取ります。 ビューが実行されないときにテーブルからフェッチした結果をキャッシュします。 同時に、クエリキャッシュメカニズムは、ユーザーがテーブルまたはビューにアクセスするかどうかに関係なく、ユーザークエリのレベルで機能します。



ビューは、テーブルと他のビューの両方に基づくことができます。 ネストできます（最大32のネストレベル）。

ビューを使用する利点：

1. 権限はテーブルではなくプレゼンテーションに付与されるため、データへのアクセス権を柔軟に設定できます。 これは、ユーザーがテーブルの個々の行に権限を与える必要がある場合、またはデータ自体ではなく、それらに対する何らかのアクションの結果を受け取る機能を必要とする場合に非常に便利です。
2. データストレージとソフトウェアのロジックを分離できます。 プログラムコードに影響を与えずにデータ構造を変更できます。アプリケーションがアクセスするために使用したテーブルに似たビューを作成するだけで済みます。 これは、プログラムコードを変更する方法がない場合や、異なるデータ構造要件を持つ複数のアプリケーションが同じデータベースにアクセスする場合に非常に便利です。
3. 行や列の特定の部分へのアクセス、複数のテーブルからのデータの取得、さまざまな機能を使用したデータの変換などのアクションの自動実行による使いやすさ。

MySQLの制限を表示する

この記事では、MySQLバージョン5.1の制限について説明しています（将来、その数は削減される可能性があります）。

• ビューにトリガーを配置することはできません
• 一時テーブルに基づいてプレゼンテーションを作成することはできません。 一時的なプレゼンテーションを行うことはできません。
• ビュー定義のFROM部分でサブクエリを使用することはできません。
• システム変数とユーザー変数はビュー定義で使用できません。 ストアドプロシージャ内では、ビュー定義でローカル変数またはプロシージャパラメータを使用できません。
• 準備された式のパラメータ（PREPARE）は、ビューの定義では使用できません。
• ビュー定義に存在するテーブルとビューが存在する必要があります。
• 多くの要件を満たすビューのみが、UPDATE、DELETE、INSERTなどのクエリを許可します。

ビューを作成する

ビューを作成するには、次の構文のCREATE VIEWステートメントを使用します。

CREATE [ OR REPLACE]

[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]



* This source code was highlighted with Source Code Highlighter .

view_nameは、作成するビューの名前です。 select_statement-ビューに含まれるテーブルやビューからデータを選択するSELECTステートメント



CREATE VIEWステートメントには、4つのオプションの構成体が含まれています。

1. OR REPLACE-この構成を使用すると、同じ名前のビューが存在する場合、古いビューが削除され、新しいビューが作成されます。 そうしないと、この名前のビューの存在を通知するエラーが発生し、新しいビューは作成されません。 1つの機能に注意する必要があります。1つのデータベース内のテーブルとビューの名前は一意である必要があります。 既存のテーブルの名前でビューを作成することはできません。 ただし、OR REPLACE構文はビューにのみ影響し、テーブルを置き換えません。
2. ALGORITM-ビューを参照するときに使用されるアルゴリズムを定義します（詳細は以下を参照）。
3. column_list-ビューフィールドの名前を設定します。
4. WITH CHECK OPTION-この構成を使用すると、追加または変更されたすべての行がビュー定義に準拠しているかどうかがチェックされます。 準拠していない場合、この変更は実装されません。 更新できないビューにこの構成を指定すると、エラーが発生し、ビューが作成されないことに注意してください。 （詳細は以下をご覧ください）。

デフォルトでは、ビュー列は、ビュー定義のSELECTステートメントによって返されるフィールドと同じ名前を持ちます。 ビューフィールド名を明示的に指定する場合、column_listには、フィールドごとに1つの名前をカンマで区切って含める必要があります。 ビューフィールドの名前を明示的に示すことをお勧めする理由は2つあります。

1. ビューフィールド名は、特定のビュー内で一意である必要があります。 複数のテーブルに基づいてビューを作成する場合、ビュー内のフィールドの名前が繰り返される可能性があります。 例：

   CREATE VIEW v AS SELECT a.id, b.id FROM a,b;



* This source code was highlighted with Source Code Highlighter .
   
   
   
   

   この状況を回避するには、ビューフィールドの名前を明示的に指定する必要があります
   
   

   CREATE VIEW v (a_id, b_id) AS SELECT a.id, b.id FROM a,b;



* This source code was highlighted with Source Code Highlighter .
   
   
   
   

   列名に同義語（エイリアス）を使用すると、同じ結果が得られます。
   
   

   CREATE VIEW v AS SELECT a.id a_id, b.id b_id FROM a,b;



* This source code was highlighted with Source Code Highlighter .
   
   
   
   

2. 表現の定義で、受信したデータがいくつかの関数を使用して変換される場合、フィールドの名前は指定された式になります。これは、このフィールドへのさらなる参照にはあまり便利ではありません。 例：
   
   

   CREATE VIEW v AS SELECT group_concat( DISTINCT column_name oreder BY column_name separator '+' ) FROM table_name;



* This source code was highlighted with Source Code Highlighter .
   
   
   
   

   将来的にフィールド名として「group_concat（DISTINCT username ORDER BY username separator '+'）」を使用するのはほとんど便利ではありません

ビューの内容を表示するには、SELECTステートメントを使用します（単純なテーブルの場合とまったく同じ方法で）。一方、SELECTステートメントはビュー定義自体にあります。 ネストされたデザイン、つまりリクエスト内のリクエストが判明します。 ただし、SELECTステートメントの一部の構造は、両方のステートメントに存在する場合があります。 考えられるシナリオは3つあります。両方とも実行され、そのうちの1つは無視され、結果は不確実です。 これらのケースをより詳細に検討しましょう。

1. WHERE句が両方の演算子で発生する場合、これらの条件は両方ともAND演算子で結合されたかのように満たされます。
2. ビュー定義にORDER BY句がある場合、ビューにアクセスする外部SELECTステートメントに独自のソート条件がない場合にのみ機能します。 外部ステートメントにORDER BY句がある場合、ビュー定義での並べ替えは無視されます。
3. HIGH_PRIORITYなど、ロックメカニズムに影響する修飾子が両方の演算子にある場合、それらの結合アクションの結果は未定義です。 あいまいさを避けるため、ビューの定義ではこのような修飾子を使用しないことをお勧めします。

表現アルゴリズム

MySQLがビューにアクセスするときに使用する2つのアルゴリズムがあります：MERGEとTEMPTABLE。



MERGEアルゴリズムの場合、MySQLはビューにアクセスするときに、ビューの定義から対応する部分を使用中のステートメントに追加し、結果のステートメントを実行します。



TEMPTABLEアルゴリズムの場合、MySQLはビューの内容を一時テーブルに書き込みます。一時テーブルでは、ビューに面しているステートメントが実行されます。

注 ：このアルゴリズムを使用する場合、ビューは更新できません（以下を参照）。



ビューを作成するとき、オプションの構成[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

未定義とは、MySQL自体がビューにアクセスするときに使用するアルゴリズムを選択することを意味します。 この構成が欠落している場合、これがデフォルト値です。



MERGEアルゴリズムを使用するには、テーブルの行とそれに基づいたプレゼンテーションの間に1対1の対応が必要です。



プレゼンテーションで、フォーラムトピックの回答数に対するビュー数の比率を選択してみましょう。

CREATE VIEW v AS SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0;



* This source code was highlighted with Source Code Highlighter .

このビューでは、各行はトピックテーブルの単一の行に対応しています。 MERGEアルゴリズムを使用できます。 私たちの見解に対する次の訴えを考慮してください。

SELECT subject, param FROM v WHERE param>1000;



* This source code was highlighted with Source Code Highlighter .

MERGEアルゴリズムの場合、MySQLは使用されるSELECTステートメントにビューの定義を含めます：ビューの名前をテーブルの名前に置き換え、フィールドのリストをビューのフィールドの定義に置き換え、AND演算子を使用してWHERE句に条件を追加します。 MySQLによって実行される結果のステートメントは次のとおりです。

SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0 AND num_views/num_replies>1000;



* This source code was highlighted with Source Code Highlighter .

グループ関数（count、max、avg、group_concatなど）、フィールドの列挙に関するサブクエリ、またはDISTINCT、GROUP BY構文がビューの定義で使用されている場合、テーブルの行間の1対1の対応彼女の提出。



プレゼンテーションで各フォーラムのトピック数を選択してください。

CREATE VIEW v AS SELECT forum_id, count (*) AS num FROM topics GROUP BY forum_id;



* This source code was highlighted with Source Code Highlighter .

フォーラムでトピックの最大数を見つけます。

SELECT MAX (num) FROM v;



* This source code was highlighted with Source Code Highlighter .

MERGEアルゴリズムが使用された場合、このクエリは次のように変換されます。

SELECT MAX ( count (*)) FROM topics GROUP BY forum_id;



* This source code was highlighted with Source Code Highlighter .

グループ関数のネストが使用されているため、このクエリを実行するとエラー「ERROR 1111（HY000）：Invalid USE of GROUP function」が発生します。



この場合、MySQLはTEMPTABLEアルゴリズムを使用します。 ビューの内容を一時テーブルに入れ（このプロセスは「ビューの具体化」とも呼ばれます）、一時テーブルのデータを使用してMAX（）を計算します。

CREATE TEMPORARY TABLE tmp_table SELECT forum_id, count (*) AS num FROM topics GROUP BY forum_id;

SELECT MAX (num) FROM tmp_table;

DROP TABLE tpm_table;



* This source code was highlighted with Source Code Highlighter .

要約すると、ビューを作成するときにアルゴリズムを明示的に示す重大な理由はないことに注意してください。

1. UNDEFINEDの場合、MySQLはTEMPTABLEよりも効率的であり、ビューとは異なり、ビューを更新できないため、可能な限りMERGEを使用しようとします。
2. MERGEを明示的に指定し、ビュー定義にその使用を禁止する構造が含まれている場合、MySQLは警告を発行し、値をUNDEFINDに設定します。

更新可能性の表示

ビューは、UPDATEおよびDELETEステートメントを適用して、ビューの基になっているテーブルのデータを変更できる場合、更新可能と呼ばれます。 プレゼンテーションを更新するには、2つの条件が満たされている必要があります。

1. ビューの行とビューの基になるテーブルとの間の1対1の対応、つまり ビューの各行は、ソーステーブルの1行に対応する必要があります。
2. プレゼンテーションフィールドは、テーブルフィールドの単純な列挙であり、式col1 / col2またはcol1 + 2ではありません。

注意 ：更新されたビューが単一のテーブルに基づいているためのロシア語の文献に見られる要件と、主キー表現のフィールド数における物理テーブルの存在は必要ありません。 ほとんどの場合、単一のテーブルの要件は変換エラーです。 実際には、複数のテーブルに基づいたビューを使用すると、クエリごとに1つのテーブルのみを更新できます。 UPDATEステートメントのSET構造は、ビュー定義から1つのテーブルの列のみをリストする必要があります。 さらに、複数テーブルビューを更新可能にするには、その定義内のテーブルを結合するのはINNER JOINのみで、OUTER JOINまたはUNIONは使用しないでください。



更新されたビューでは、ビューに存在しないソーステーブルのすべてのフィールドにデフォルト値がある場合、データの追加（INSERT）が許可される場合があります。



注 ：複数のテーブルに基づくビューでは、データを追加する操作（INSERT）は、単一の実際のテーブルに追加がある場合にのみ機能します。 これらのビューのDELETEはサポートされていません。



WITH [CASCADED | LOCAL] CHECK OPTION追加または変更されたすべての行は、ビュー定義に準拠しているかどうかがチェックされます。

• データ変更（UPDATE）は、新しい値を持つ行がビュー定義のWHERE句を満たす場合にのみ発生します。
• データの追加（INSERT）は、新しい行がビュー定義のWHERE句を満たす場合にのみ発生します。

つまり、ビューでデータに追加または変更して、ビューからアクセスできないようにすることはできません。



キーワードCASCADEDおよびLOCALは、他のビューに基づくビューの検証の深さを定義します。

• LOCALの場合、WHERE句は、ビュー自体の定義でのみチェックされます。
• CASCADEDの場合、このビューが基づいているすべてのビューに対してチェックが実行されます。 デフォルト値はCASCADEDです。

2つのテーブルに基づいて更新されたビューの例を考えてみましょう。 プレゼンテーションで2000を超えるビューを持つフォーラムトピックを選択してください。

punbb > CREATE OR REPLACE VIEW v AS

-> SELECT forum_name, `subject`, num_views FROM topics,forums f

-> WHERE forum_id=f.id AND num_views>2000 WITH CHECK OPTION ;

Query OK, 0 rows affected (0.03 sec)



punbb > SELECT * FROM v WHERE subject= 'test' ;

+------------+---------+-----------+

| forum_name | subject | num_views |

+------------+---------+-----------+

| | test | 3000 |

+------------+---------+-----------+

1 row IN SET (0.03 sec)



punbb > UPDATE v SET num_views=2003 WHERE subject= 'test' ;

Query OK, 0 rows affected (0.03 sec)

Rows matched: 1 Changed: 0 WARNINGS: 0



punbb > SELECT * FROM v WHERE subject= 'test' ;

+------------+---------+-----------+

| forum_name | subject | num_views |

+------------+---------+-----------+

| | test | 2003 |

+------------+---------+-----------+

1 row IN SET (0.01 sec)



punbb > SELECT subject, num_views FROM topics WHERE subject= 'test' ;

+---------+-----------+

| subject | num_views |

+---------+-----------+

| test | 2003 |

+---------+-----------+

1 rows IN SET (0.01 sec)



* This source code was highlighted with Source Code Highlighter .

ただし、num_views値を2000未満に設定しようとすると、新しい値はビュー定義のWHERE num_views> 2000条件を満たさず、更新は発生しません。

punbb > UPDATE v SET num_views=1999 WHERE subject= 'test' ;

ERROR 1369 (HY000): CHECK OPTION failed 'punbb.v'



* This source code was highlighted with Source Code Highlighter .

すべての更新されたビューがデータの追加を許可するわけではありません：

punbb > INSERT INTO v (subject,num_views) VALUES ( 'test1' ,4000);

ERROR 1369 (HY000): CHECK OPTION failed 'punbb.v'



* This source code was highlighted with Source Code Highlighter .

理由は、forum_id列のデフォルト値が0であるため、追加される行がビュー定義のWHERE条件forum_id = f.idを満たさないためです。 ビュー定義にそのようなフィールドがないため、forum_idの値を明示的に示すことはできません。

punbb > INSERT INTO v (forum_id,subject,num_views) VALUES (1, 'test1' ,4000);

ERROR 1054 (42S22): Unknown COLUMN 'forum_id' IN 'field list'



* This source code was highlighted with Source Code Highlighter .

一方、

punbb > INSERT INTO v (forum_name) VALUES ( 'TEST' );

Query OK, 1 row affected (0.00 sec)



* This source code was highlighted with Source Code Highlighter .

したがって、2つのテーブルに基づくビューでは、両方のテーブルを更新し、一方のテーブルのみにデータを追加できます。



あなたの提出で幸運を！



SQLinfoを使用したMySQLのクロスポストビュー（VIEW） 。