MySQLにおけるビューの作成（CREATE VIEW）と使用制限の詳しい説明

この記事では、例を使用して、MySQL ビューの作成 (CREATE VIEW) と使用上の制限について説明します。ご参考までに、詳細は以下の通りです。

MySQL 5.x 以降のバージョンでは、データベースビューがサポートされています。MySQL では、ビューのほぼすべての機能が SQL: 2003 標準に準拠しています。 MySQL は、ビューに対するクエリを 2 つの方法で処理します。

最初の方法では、MySQL はビュー定義ステートメントに基づいて一時テーブルを作成し、この一時テーブルに対して受信クエリを実行します。
2 番目の方法では、MySQL は受信したクエリとクエリ定義を 1 つのクエリに結合し、結合されたクエリを実行します。

MySQL はビューのバージョン管理システムをサポートしています。ビューが変更または置換されるたびに、ビューのコピーが特定のデータベースフォルダにある arc (アーカイブ) フォルダにバックアップされます。バックアップファイルの名前は view_name.frm-00001 です。ビューを再度変更すると、mysql は view_name.frm-00002 という名前の新しいバックアップファイルを作成します。 MySQL では、他のビューに基づいてビューを作成できます。つまり、ビュー定義の SELECT ステートメントで、別のビューを参照できます。

まあ、これ以上詳しく説明することはしません。次に、CREATE VIEW ステートメントを使用してビューを作成してみましょう。まずは構文構造を見てみましょう。

作成する 
  [アルゴリズム = {MERGE | TEMPTABLE | 未定義}]
VIEW [データベース名].[ビュー名] 
として
[SELECT文]

それでは、上記の SQL 内のさまざまな単語の意味を詳しく見てみましょう。まず、最初の括弧はビューを作成するためのアルゴリズム属性を表します。これにより、ビューを作成するときに MySQL が使用するメカニズムを制御できます。MySQL では、MERGE、TEMPTABLE、および UNDEFINED の 3 つのアルゴリズムが提供されています。それぞれを個別に見てみましょう。

MySQL は MERGE アルゴリズムを使用して、最初に入力クエリとビューを定義する SELECT ステートメントを 1 つのクエリに結合します。次に、MySQL は結合されたクエリを実行して結果セットを返します。選択ステートメントに集計関数 (min、max、sum、count、avg など) または distinctive、group by、having、limit、union、union all、subquery が含まれている場合、MERGE アルゴリズムは許可されません。選択ステートメントがテーブルを参照しない場合、MERGE アルゴリズムは許可されません。 MERGE アルゴリズムが許可されていない場合、MySQL はアルゴリズムを UNDEFINED に変更します。入力クエリとビュー定義内のクエリを 1 つのクエリに結合することをビュー解決と呼びます。
TEMPTABLE アルゴリズムを使用すると、MySQL は最初にビューを定義する SELECT ステートメントに基づいて一時テーブルを作成し、次に一時テーブルに対して入力クエリを実行します。 MySQL は結果セットを保存するために一時テーブルを作成し、データを基本テーブルから一時テーブルに移動する必要があるため、TEMPTABLE アルゴリズムは MERGE アルゴリズムよりも効率が低くなります。さらに、TEMPTABLE アルゴリズムを使用するビューは更新できません。
明示的なアルゴリズムを指定せずにビューを作成する場合、UNDEFINED がデフォルトのアルゴリズムになります。 UNDEFINED アルゴリズムにより、MySQL は MERGE アルゴリズムと TEMPTABLE アルゴリズムのどちらを使用するかを選択できます。 MySQL では、MERGE アルゴリズムの方が効率的であるため、TEMPTABLE アルゴリズムよりも MERGE アルゴリズムが優先されます。

次に、view の後に続くフレーズは名前を意味します。データベースでは、ビューとテーブルは同じ名前空間を共有するため、ビューとテーブルに同じ名前を付けることはできません。さらに、ビューの名前はテーブルの命名規則に従う必要があります。

最後のステートメントは SELECT ステートメントです。 SELECT ステートメントでは、データベース内の任意のテーブルまたはビューからデータをクエリできます。同時に、SELECT ステートメントは次のルールに従う必要があります。

SELECT ステートメントでは、where 句にサブクエリを含めることができますが、FROM 句にサブクエリを含めることはできません。
SELECT ステートメントは、ローカル変数、ユーザー変数、セッション変数などの変数を参照できません。
SELECT ステートメントは準備されたステートメントのパラメータを参照できません。

ここで注意すべき点は、SELECT ステートメントではテーブルを参照する必要がないことです。最後に、orderDetails テーブルに基づいて各注文の合計売上を表すビューを作成してみます。

VIEW SalePerOrder AS を作成する
  選択 
    注文番号、SUM(注文数量 * 各価格) 合計
  から
    注文詳細
  注文番号によるグループ化
  ORDER BY total DESC;

SHOW TABLES コマンドを使用してサンプルデータベース (yiibaidb) 内のすべてのテーブルを表示すると、テーブルのリストに SalesPerOrder ビューも表示されることがわかります。

mysql> テーブルを表示;
+--------------------+
| yiibaidb 内のテーブル |
+--------------------+
| 記事タグ |
| 連絡先 |
| 顧客 |
| 部門 |
|従業員|
| オフィス |
|オフィス_bk|
| 米国オフィス |
|注文詳細|
| 注文 |
| お支払い |
| 製品ライン |
| 製品 |
|販売注文|
+--------------------+
14行セット

これは、ビューとテーブルが同じ名前空間を共有するためです。どのオブジェクトがビューかテーブルかを確認するには、次のように SHOW FULL TABLES コマンドを使用します。

mysql> テーブル全体を表示します。
+--------------------+-------------+
| yiibaidb 内のテーブル | テーブルタイプ |
+--------------------+-------------+
| article_tags | ベーステーブル |
| 連絡先 | ベーステーブル |
| 顧客 | ベース テーブル |
| 部門 | ベース テーブル |
| 従業員 | ベース テーブル |
| オフィス | ベーステーブル |
| offices_bk | ベーステーブル |
| offices_usa | ベース テーブル |
| 注文詳細 | 基本テーブル |
| 注文 | ベース テーブル |
| 支払い | ベース テーブル |
| 製品ライン | ベーステーブル |
| 製品 | ベーステーブル |
| セールパーオーダー | 表示 |
+--------------------+-------------+
14行セット

結果セットの table_type 列は、どのオブジェクトがビューで、どのオブジェクトがテーブル (基本テーブル) であるかを指定します。上記のように、saleperorder に対応する table_type 列の値は VIEW です。ただし、各販売注文の合計売上額を照会する場合は、次に示すように、SalePerOrder ビューで単純な SELECT ステートメントを実行するだけで済みます。

選択 
  *
から
  注文ごとの販売;

上記のクエリステートメントを実行すると、次の結果が得られます。

+-------------+-----------+
| 注文数 | 合計 |
+-------------+-----------+
| 10165 | 67392.85 |
| 10287 | 61402.00 |
| 10310 | 61234.67 |
| 10212 | 59830.55 |
|-- ここでは多くのデータが省略されています-- |
| 10116 | 1627.56 |
| 10158 | 1491.38 |
| 10144 | 1128.20 |
| 10408 | 615.45 |
+-------------+-----------+
セット内の行数は 327 行です

別のビューに基づいてビューを作成しましょう。たとえば、次に示すように、SalesPerOrder ビューに基づいて BigSalesOrder というビューを作成し、合計が 60,000 を超える各販売注文を表示できます。

VIEW BigSalesOrder AS を作成する
  選択 
    orderNumber、ROUND(total,2) を合計として計算
  から
    注文あたりの販売数
  どこ
    合計 > 60000;

これで、次のように BigSalesOrder ビューからデータをクエリできます。

選択 
  注文数、合計
から
  ビッグセールスオーダー;

上記のクエリステートメントを実行すると、次の結果が得られます。

+-------------+-----------+
| 注文数 | 合計 |
+-------------+-----------+
| 10165 | 67392.85 |
| 10287 | 61402.00 |
| 10310 | 61234.67 |
+-------------+-----------+
3行セット

次に、内部結合を使用して、顧客番号と顧客が支払った合計金額を含むビューを次のように作成します。

CREATE VIEW customerOrders AS
  選択 
    c.顧客番号、
    金額
  から
    顧客
      内部結合
    支払い p ON p.customerNumber = c.customerNumber
  GROUP BY c.顧客番号
  ORDER BY p.amount DESC;

次の SQL を使用して、customerOrders ビューのデータをクエリします。

+----------------+-----------+
| 顧客番号 | 金額 |
+----------------+-----------+
| 124 | 101244.59 |
| 321 | 85559.12 |
| 239 | 80375.24 |
| **** ここでは多くのデータが省略されています ***|
| 219 | 3452.75 |
| 216 | 3101.4 |
| 161 | 2434.25 |
| 172 | 1960.8 |
+----------------+-----------+
セット内の行数は 98 行

ここで、次のようにサブクエリを使用して、すべての製品の平均価格よりも高い価格の製品を含むビューを作成してみます。

上記のAvgProducts ASのビューを作成
  選択 
    製品コード、製品名、購入価格
  から
    製品
  どこ
    購入価格 > 
 （選択 
        AVG(購入価格)
      から
        製品）
  ORDER BY buyPrice DESC;

上記のAvgProductsビューのデータをクエリしてみましょう。

選択 
  *
から
  上記の平均製品;

上記のクエリステートメントを実行すると、次の結果が得られます。

+-------------+------------------------------------------+----------+
| 製品コード | 製品名 | 購入価格 |
+-------------+------------------------------------------+----------+
| S10_4962 | 1962 ランチアA デルタ 16V | 103.42 |
| S18_2238 | 1998 クライスラー プリマス プラウラー | 101.51 |
| S10_1949 | 1952 アルピーヌ ルノー 1300 | 98.58 |
|**************** ここでは多くのデータが省略されています************************************|
| S18_3320 | 1917 マクスウェル ツーリングカー | 57.54 |
| S24_4258 | 1936 クライスラー エアフロー | 57.46 |
| S18_3233 | 1985 トヨタ スープラ | 57.01 |
| S18_2870 | 1999 インディ 500 モンテカルロ SS | 56.76 |
| S32_4485 | 1974 ドゥカティ 350 Mk3 デスモ | 56.13 |
| S12_4473 | 1957 シボレー ピックアップ | 55.7 |
| S700_3167 | F/A 18 ホーネット 1/72 | 54.4 |
+-------------+------------------------------------------+----------+
セット内の行数は54行

さて、ここではビューの作成と使用についてほぼ説明しました。しかし、ビューの使用に制限はないのでしょうか?答えはもちろん「はい」です。一つずつ見ていきましょう。

まず、ビューにインデックスを作成することはできません。次に、マージアルゴリズムを使用するビューを使用してデータをクエリする場合、MySQL は基になるテーブルのインデックスを使用します。また、テンプテーションアルゴリズムを使用するビューの場合、ビューに対してデータをクエリすると、インデックスは使用されません。

また、MySQL 5.7.7 より前のバージョンでは、SELECT ステートメントの FROM 句でサブクエリを使用してビューを定義することはできないことに注意してください。

ビューの基になるテーブルを削除したり名前を変更したりしても、MySQL はエラーを発行しません。ただし、mysql はビューを無効にするため、CHECK TABLE ステートメントを使用してビューが有効かどうかを確認できます。

単純なビューではテーブル内のデータを更新できますが、結合やサブクエリなどを含む複雑な選択ステートメントに基づいて作成されたビューは更新できません。

MySQL は、Oracle や PostgreSQL などの他のデータベースシステムのような物理ビューをサポートしていません。MySQL は物理ビューをサポートしていません。

さて、今回はビューについて言うことはこれだけです。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリスキル」、「MySQL トランザクション操作スキル」、「MySQL ストアドプロシージャスキル」、「MySQL データベースロック関連スキルの概要」、および「MySQL 共通関数の概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません: