MySQLのview(ビュー)で仮想的なテーブルを作成する方法

MySQL関連のサイトを見ているとview(ビュー)が云々という記事を見かけた。調べたところ仮想的なテーブルらしい。ただ、だいぶ前に使ったTEMPORARY TABLEとは違って一時的なテーブルではなく、明示的に削除しない限り保存されるテーブルみたい。以下に利用方法をメモ。

使用するテーブル情報

テーブル構造

mysql> SHOW COLUMNS FROM `test_table`;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
| age   | int          | NO   |     | NULL    |                |
| flg   | tinyint      | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

保存されているデータ

mysql> SELECT * FROM `test_table`;
+----+-----------+-----+-----+
| id | name      | age | flg |
+----+-----------+-----+-----+
|  1 | 山田      |  20 |   1 |
|  2 | 鈴木      |  25 |   0 |
|  3 | 佐々木    |  30 |   1 |
|  4 | 大地      |  35 |   0 |
|  5 | 佐藤      |  40 |   0 |
|  6 | 高橋      |  45 |   1 |
|  7 | 伊藤      |  50 |   0 |
+----+-----------+-----+-----+

利用方法

viewの作成

基本構文は以下の通り。

CREATE VIEW 【view名】 
AS 【SELECT文】

今回は以下SQLでviewを作成した。

CREATE VIEW test_view AS 
SELECT * 
FROM `test_table`
WHERE `flg` = 1;

viewの確認

基本的な確認方法

以下のように普通のSELECT文で確認できる。

SELECT * 
FROM test_view;

以下が結果になる。

+----+-----------+-----+-----+
| id | name      | age | flg |
+----+-----------+-----+-----+
|  1 | 山田      |  20 |   1 |
|  3 | 佐々木    |  30 |   1 |
|  6 | 高橋      |  45 |   1 |
+----+-----------+-----+-----+

元テーブルを編集した場合

ちなみに元テーブル(test_table)にデータを登録した場合、

INSERT INTO `test_table` 
    (`name`, `age`, `flg`) 
VALUES 
    ("渡辺", 55, 1);

以下のようにview側のテーブルにも反映される。

mysql> SELECT * FROM test_view;
+----+-----------+-----+-----+
| id | name      | age | flg |
+----+-----------+-----+-----+
|  1 | 山田      |  20 |   1 |
|  3 | 佐々木    |  30 |   1 |
|  6 | 高橋      |  45 |   1 |
|  8 | 渡辺      |  55 |   1 |
+----+-----------+-----+-----+

WHERE句で検索も可能

以下のようにview側のテーブルに対してWHERE句で検索も可能。

SELECT * 
FROM test_view
WHERE `age` > 20;

+----+-----------+-----+-----+
| id | name      | age | flg |
+----+-----------+-----+-----+
|  3 | 佐々木    |  30 |   1 |
|  6 | 高橋      |  45 |   1 |
|  8 | 渡辺      |  55 |   1 |
+----+-----------+-----+-----+

viewの削除

DROP VIEW test_view;

リファレンス

https://dev.mysql.com/doc/refman/8.0/ja/create-view.html

所感

使いどころ

実運用だと以下のようなケースで使えそう。

• 同じテーブルでもサイト側とダッシュボード(管理画面)側で表示するデータが異なったりする(非表示フラグが立っている場合にサイト側では非表示、ダッシュボード側では表示等)ようなケース
• 複数のJOINがあってSQL文が長いようなケース

更新時の注意点

view側のテーブルに更新する際は色々制限がある模様。基本的には元テーブルを更新し、view側のテーブルはSELECTのみで使うような形が安全かも。