MySQLでビューを使う

2016.01.27

はじめに

MySQLの理解を深めるために調査した結果を記事にします。
今回はビューを使用し、想定する状況に対応する例を考えてみました。

想定する状況

・本屋(2店舗)に関する情報を管理している。
・テーブルは3つ。
・依頼に応じたデータを提出する必要がある。
・関連するテーブルの内容を手間無く取得できる様にしておきたい。

テーブル

stores(店舗ごとの在庫)

id=行番号、store_id=店番号、item_id=商品番号、items=在庫数

+----+----------+---------+-------+
| id | store_id | item_id | items |
+----+----------+---------+-------+
|  1 |        1 |       1 |    10 |
|  2 |        1 |       2 |    30 |
|  3 |        1 |       3 |   100 |
|  4 |        2 |       1 |    15 |
|  5 |        2 |       4 |    25 |
|  6 |        2 |       5 |    15 |
|  7 |        2 |       3 |   120 |
+----+----------+---------+-------+
store_info(店舗情報)

store_id=店番号、store_name=店名、area=地区

+----------+------------+-------+
| store_id | store_name | area  |
+----------+------------+-------+
|        1 | store_A    | tokyo |
|        2 | store_B    | osaka |
+----------+------------+-------+
item_info(商品情報)

item_id=商品番号、item_name=商品名、price=商品価格、genre=種別

+---------+-----------+-------+----------+
| item_id | item_name | price | genre    |
+---------+-----------+-------+----------+
|       1 | book1     |   500 | novel    |
|       2 | book2     |  1200 | novel    |
|       3 | book3     |   200 | magazine |
|       4 | book4     |   800 | magazine |
|       5 | book5     |   750 | cook     |
|       6 | book6     |  3000 | art      |
+---------+-----------+-------+----------+

テーブルをビューにまとめる

3つのテーブルを1つにまとめます。

SQL文

CREATE VIEW
  view1
  (  
    store_id, 
    item_id, 
    items, 
    item_name, 
    price, 
    genre,
    store_name,
    area 
  )
AS
  SELECT
    T.store_id, 
    T.item_id, 
    T.items, 
    I.item_name, 
    I.price, 
    I.genre,
    S.store_name,
    S.area 
  FROM
    stores AS T 
    LEFT JOIN 
    item_info AS I 
    ON
    T.item_id=I.item_id
      LEFT JOIN
      store_info AS S
      ON
      T.store_id=S.store_id;

実行結果

テーブルと同じ様に扱えるので今後の作業が楽になります。

mysql> select * from view1;
+----------+---------+-------+-----------+-------+----------+------------+-------+
| store_id | item_id | items | item_name | price | genre    | store_name | area  |
+----------+---------+-------+-----------+-------+----------+------------+-------+
|        1 |       1 |    10 | book1     |   500 | novel    | store_A    | tokyo |
|        1 |       2 |    30 | book2     |  1200 | novel    | store_A    | tokyo |
|        1 |       3 |   100 | book3     |   200 | magazine | store_A    | tokyo |
|        2 |       1 |    15 | book1     |   500 | novel    | store_B    | osaka |
|        2 |       4 |    25 | book4     |   800 | magazine | store_B    | osaka |
|        2 |       5 |    15 | book5     |   750 | cook     | store_B    | osaka |
|        2 |       3 |   120 | book3     |   200 | magazine | store_B    | osaka |
+----------+---------+-------+-----------+-------+----------+------------+-------+

テーブル確認

ビュー「view1」が含まれています。

mysql> show tables from blog;
+----------------+
| Tables_in_blog |
+----------------+
| item_info      |
| store_info     |
| stores         |
| view1          |
+----------------+

解説

構文
CREATE VIEW "ビュー名" ("列名1","列名2",...) AS <SELECT文>

2行目でビューの名前、
4〜11行目でビューの列名を決めています。

CREATE VIEW
  view1
  (  
    store_id, 
    item_id, 
    items, 
    item_name, 
    price, 
    genre,
    store_name,
    area 
  )
AS

値を抜き出す列を指定しています。
上から順に4〜11行目で設定した列に値が割り振られます。

  SELECT
    SS.store_id, 
    SS.item_id, 
    SS.items, 
    I.item_name, 
    I.price, 
    I.genre,
    S.store_name,
    S.area

24〜28行目で最初のテーブル結合設定、
29〜32行目で最初のテーブル結合の結果に次のテーブル結合を設定しています。

  FROM
    stores AS SS 
    LEFT JOIN 
    item_info AS I 
    ON
    SS.item_id=I.item_id
      LEFT JOIN
      store_info AS S
      ON
      SS.store_id=S.store_id;

テーブル結合についてはMySQLでテーブル結合で解説しています。

まとめ

ビューはデータを持たず、実行文が入っているだけです。
例えば、SELECT * FROM "ビュー名"を実行するとCREATE VIEWで設定したSELECT文が実行されます
ただしビューは一データベースに格納されるので、テーブルと同じ様に扱えます。

利点

・テーブルを持たないので容量を使わないがテーブルと同じ様に扱える。
・繋がりのある複数のテーブルを一つのテーブルの様に扱えるので見通しが良くなる。
・最新の情報を参照するので、元データを変更した場合でもテーブルを作り直す手間が無くなる。

欠点

・毎回テーブル生成処理を行うので、使い方によっては全体の処理が遅くなる。

利点欠点を理解して上手く使いたいですね。