MySQL - VIEW

概要

MySQLのVIEW (ビュー) は、1つ以上のテーブルから導出される仮想的なテーブルである。
VIEWは、SELECT文の結果に名前を付けて保存したもので、実データを持たず、クエリ定義のみを格納する。

VIEWを使用することで、複雑なクエリの簡素化、セキュリティの向上、データ抽象化が実現できる。
アプリケーションは、VIEWを通常のテーブルと同様に扱うことができ、SELECT文でデータを取得できる。

MySQLでは、VIEWの作成、変更、削除、確認のための構文が提供されている。
また、条件を満たすVIEWは更新可能ビュー (Updatable View) として動作し、INSERT、UPDATE、DELETE 操作が可能である。

VIEWには、MERGE (マージ) と TEMPTABLE (テンポラリテーブル) の2つのアルゴリズムがあり、MySQLが自動的に選択するか、明示的に指定できる。
MERGEアルゴリズムはVIEWの定義をクエリに統合してパフォーマンスに優れるが、TEMPTABLEアルゴリズムは一時テーブルを使用するため更新不可となる。

VIEWは、アクセス制御、複雑なJOINの簡素化、計算列の提供、テーブル構造の変更からアプリケーションを保護する目的で使用される。
ただし、MySQLにはマテリアライズドビュー (実体化ビュー) が存在しないため、集計結果をキャッシュする場合はテーブルとトリガーで代替する必要がある。

VIEWには、参照できるテーブル数 (最大61個)、インデックス作成不可、ビュー定義で使用されているテーブルの変更によるビュー無効化等の制限事項がある。

VIEWの作成

基本構文

VIEWを作成するには、CREATE VIEW 文を使用する。

 CREATE VIEW <ビュー名> AS
 SELECT <列名 1>, <列名 2>, ...
 FROM <テーブル名>
 WHERE <条件式>;

基本的なVIEWの作成を以下に示す。

 CREATE VIEW active_users AS
 SELECT user_id, username, email
 FROM users
 WHERE status = 'active';

• 複数テーブルのJOINを含むVIEWの作成例

   CREATE VIEW order_details AS
   SELECT o.order_id, o.order_date, c.customer_name, p.product_name, o.quantity
   FROM orders o
   JOIN customers c ON o.customer_id = c.customer_id
   JOIN products p ON o.product_id = p.product_id;
  

  
  

• 計算列を含むVIEWの作成例

   CREATE VIEW product_summary AS
   SELECT product_id, product_name, price, stock,
          price * stock AS total_value
   FROM products;
  

列名の指定

VIEWの列名を明示的に指定することができる。

 CREATE VIEW <ビュー名> (<列名 1>, <列名 2>, <列名 3>) AS
 SELECT <列名 1>, <列名 2>, <列名 3>
 FROM <テーブル名>;

列名を指定する場合は、SELECT文の列数と一致する必要がある。

 CREATE VIEW user_info (id, name, mail) AS
 SELECT user_id, username, email
 FROM users;

列名を省略した場合は、SELECT文の列名がそのまま使用される。
ただし、計算列や重複列名がある場合は、列名を明示的に指定する必要がある。

 CREATE VIEW total_sales (year, month, total) AS
 SELECT YEAR(order_date), MONTH(order_date), SUM(amount)
 FROM orders
 GROUP BY YEAR(order_date), MONTH(order_date);

OR REPLACEオプション

OR REPLACEオプションを使用すると、既存のVIEWがある場合は置き換え、存在しない場合は新規作成される。

 CREATE OR REPLACE VIEW <ビュー名> AS
 SELECT <列名 1>, <列名 2>
 FROM <テーブル名>
 WHERE <条件式>;

CREATE OR REPLACE VIEW は、DROP VIEW と CREATE VIEW を組み合わせた操作と同等であるが、アトミックに実行される。

 CREATE OR REPLACE VIEW active_users AS
 SELECT user_id, username, email, created_at
 FROM users
 WHERE status = 'active' AND deleted_at IS NULL;

ALGORITHMオプション

ALGORITHMオプションにより、VIEWの処理方法を指定できる。

 CREATE
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW <ビュー名> AS
    SELECT ...;

ALGORITHMオプション

アルゴリズム	説明
UNDEFINED (デフォルト)	MySQLが自動的にMERGEまたはTEMPTABLEを選択する 可能な限りMERGEを使用し、不可能な場合はTEMPTABLEを使用する
MERGE	VIEWの参照をVIEW定義で置き換えて基底テーブルと結合する 基底テーブルのインデックスが使用可能 パフォーマンスに優れる 更新可能ビューとして動作する可能性がある
TEMPTABLE	VIEWの結果を一時テーブルに格納してから処理する 基底テーブルのインデックスは使用不可 GROUP BY、DISTINCT、UNION、サブクエリ等を含む場合に使用される 更新不可

MERGEアルゴリズムを明示的に指定する例を以下に示す。

 CREATE ALGORITHM=MERGE VIEW simple_view AS
 SELECT user_id, username
 FROM users
 WHERE status = 'active';

TEMPTABLEアルゴリズムが必要な場合の例を以下に示す。

 CREATE ALGORITHM=TEMPTABLE VIEW aggregated_view AS
 SELECT product_id, COUNT(*) AS order_count, SUM(quantity) AS total_qty
 FROM orders
 GROUP BY product_id;

以下のクエリ要素を含むVIEWは、MERGEアルゴリズムを使用できない。

• 集約関数 (SUM、COUNT、MAX、MIN、AVG)
• DISTINCT
• GROUP BY
• HAVING
• UNION / UNION ALL
• サブクエリ (SELECT リスト内)
• ユーザ定義変数への代入
• LIMITとOFFSETの組み合わせ

DEFINER句 と SQL SECURITY句

DEFINER 句 と SQL SECURITY 句により、VIEWのアクセス権チェック方法を制御できる。

 CREATE
    [DEFINER = <ユーザ名@ホスト名>]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW <ビュー名> AS
    SELECT ...;

DEFINER句とSQL SECURITY句

オプション	説明
DEFINER = user@host	VIEWの所有者を指定する 省略時は、VIEWを作成したユーザが設定される SUPER権限を持つユーザは、他のユーザを指定できる
SQL SECURITY DEFINER (デフォルト)	VIEWのDEFINERの権限でアクセス権をチェックする VIEWを呼び出したユーザの権限に関係なく、DEFINERの権限で実行される セキュリティ制御に有用
SQL SECURITY INVOKER	VIEWを呼び出したユーザの権限でアクセス権をチェックする VIEWを実行するユーザが基底テーブルへのアクセス権を持つ必要がある

• DEFINER権限でアクセス制御を行う例

   CREATE DEFINER = admin@localhost
   SQL SECURITY DEFINER
   VIEW public_users AS
   SELECT user_id, username
   FROM users;
  

  
  

• INVOKER権限でアクセス制御を行う例

   CREATE SQL SECURITY INVOKER VIEW my_data AS
   SELECT *
   FROM sensitive_table
   WHERE user_id = CURRENT_USER();
  

VIEWの変更

VIEWの定義を変更するには、ALTER VIEW 文を使用する。

 ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = <ユーザ名@ホスト名>]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW <ビュー名> [(<列名リスト>)]
    AS <SELECT文>
    [WITH [CASCADED | LOCAL] CHECK OPTION];

基本的な使用例を以下に示す。

 ALTER VIEW active_users AS
 SELECT user_id, username, email, last_login
 FROM users
 WHERE status = 'active';

ALTER VIEW 文は、VIEWが存在しない場合はエラーになる。
VIEWの存在に関係なく定義を保存したい場合は、CREATE OR REPLACE VIEW 文を使用する。

 CREATE OR REPLACE VIEW active_users AS
 SELECT user_id, username, email, last_login, created_at
 FROM users
 WHERE status = 'active' AND deleted_at IS NULL;

ALTER VIEW と CREATE OR REPLACE VIEW の使い分けを以下に示す。

ALTER VIEW と CREATE OR REPLACE VIEWの比較

項目	ALTER VIEW	CREATE OR REPLACE VIEW
VIEWが存在しない場合	エラー	新規作成
VIEWが存在する場合	変更	置き換え
用途	既存VIEWの変更を明示的に行う場合	VIEWの存在に関係なく定義を保存する場合

VIEWの削除

VIEWを削除するには、DROP VIEW 文を使用する。

 DROP VIEW <ビュー名>;

複数のVIEWを一括削除する場合は、カンマ区切りでVIEW名を列挙する。

 DROP VIEW <ビュー名 1>, <ビュー名 2>, <ビュー名 3>;

VIEWが存在しない場合にエラーを発生させないようにするには、IF EXISTS 句を使用する。

 DROP VIEW IF EXISTS <ビュー名>;

DROP VIEW文には、RESTRICT と CASCADE オプションがある。

 DROP VIEW <ビュー名> [RESTRICT | CASCADE];

DROP VIEWのオプション

オプション	説明
RESTRICT (デフォルト)	VIEWを削除する。 他のVIEWが参照している場合でも削除される。
CASCADE	MySQLでは未サポート RESTRICTと同じ動作。

※注意
MySQLでは、VIEWを削除しても、そのVIEWを参照している他のVIEWは自動的に削除されない。
参照元のVIEWは無効な状態となり、アクセス時にエラーが発生する。

VIEWの確認

VIEWの定義や情報を確認するには、複数の方法がある。

SHOW CREATE VIEW

VIEWの定義を確認するには、SHOW CREATE VIEW 文を使用する。

 SHOW CREATE VIEW <ビュー名>\G

この構文により、VIEWの完全な CREATE VIEW 文が表示される。
SHOW CREATE VIEW 文を実行するには、SHOW VIEW 権限が必要である。

INFORMATION_SCHEMA.VIEWS

VIEWの詳細情報を確認するには、INFORMATION_SCHEMA.VIEWS テーブルを使用する。

 SELECT TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION, IS_UPDATABLE,
        DEFINER, SECURITY_TYPE
 FROM INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_SCHEMA = '<データベース名>';

INFORMATION_SCHEMA.VIEWSの主要なカラム

カラム名	説明
TABLE_SCHEMA	VIEWが属するデータベース名
TABLE_NAME	VIEW名
VIEW_DEFINITION	VIEWの定義 (SELECT文)
CHECK_OPTION	WITH CHECK OPTIONの設定 (NONE、CASCADED、LOCAL)
IS_UPDATABLE	更新可能ビューかどうか (YES / NO)
DEFINER	VIEWの所有者
SECURITY_TYPE	SQL SECURITYの設定 (DEFINER / INVOKER)
CHARACTER_SET_CLIENT	クライアント文字セット
COLLATION_CONNECTION	接続照合順序

特定のVIEWの情報を確認する例を以下に示す。

 SELECT TABLE_NAME, IS_UPDATABLE, DEFINER, SECURITY_TYPE
 FROM INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'active_users';

SHOW FULL TABLES

データベース内のテーブルとVIEWの一覧を確認するには、SHOW FULL TABLES 文を使用する。

 SHOW FULL TABLES FROM <データベース名> WHERE Table_type = 'VIEW';

この構文により、VIEWのみを抽出して表示できる。

 SHOW FULL TABLES;

Table_type カラムの値が"VIEW"である行が、VIEWである。

更新可能ビュー

更新可能ビューの条件

条件を満たすVIEWは、更新可能ビュー (Updatable View) として動作し、INSERT、UPDATE、DELETE操作が可能である。

更新可能ビューの条件を以下に示す。

• SELECT内に集約関数 (SUM、COUNT、MAX、MIN、AVG) がない
• DISTINCTがない
• GROUP BYがない
• HAVINGがない
• UNION / UNION ALLがない
• サブクエリがSELECTリスト内に式として存在しない
• FROM句に更新不可能なVIEWがない
• FROM句に複数テーブルのJOINがない (特定条件下ではJOINビューも更新可能)
• SELECTリスト内にリテラルのみの列がある場合、挿入不可 (ただし更新・削除は可能)

更新可能ビューの例を以下に示す。

 CREATE VIEW active_users AS
 SELECT user_id, username, email
 FROM users
 WHERE status = 'active';
 
 -- 更新操作が可能
 UPDATE active_users SET email = 'new@example.com' WHERE user_id = 1;
 
 -- 挿入操作が可能
 INSERT INTO active_users (user_id, username, email)
 VALUES (100, 'newuser', 'newuser@example.com');
 
 -- 削除操作が可能
 DELETE FROM active_users WHERE user_id = 1;

更新不可能なビューの例を以下に示す。

 CREATE VIEW user_summary AS
 SELECT status, COUNT(*) AS user_count
 FROM users
 GROUP BY status;
 
 -- 更新操作不可 (集約関数とGROUP BYを含むため)
 -- UPDATE user_summary SET user_count = 10 WHERE status = 'active';  -- エラー

VIEWが更新可能かどうかを確認するには、INFORMATION_SCHEMA.VIEWS テーブルの IS_UPDATABLE カラムを確認する。

 SELECT TABLE_NAME, IS_UPDATABLE
 FROM INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_SCHEMA = '<データベース名>';

IS_UPDATABLE が YES の場合、VIEWは更新可能である。

JOINを含むVIEWの場合、以下に示す条件を満たせば更新可能である。

• 更新対象のカラムが1つのテーブルに属している
• JOIN条件が適切に設定されている
• 外部キー制約が設定されている (推奨)

JOINビューの更新例を以下に示す。

 CREATE VIEW customer_orders AS
 SELECT o.order_id, o.order_date, o.customer_id, c.customer_name
 FROM orders o
 JOIN customers c ON o.customer_id = c.customer_id;
 
 -- 更新可能 (ordersテーブルのカラムのみ更新)
 UPDATE customer_orders SET order_date = '2024-01-01' WHERE order_id = 1;

WITH CHECK OPTION

WITH CHECK OPTION 句を使用すると、VIEWのWHERE条件を満たさないデータの挿入・更新を禁止できる。

 CREATE VIEW active_users AS
 SELECT user_id, username, email, status
 FROM users
 WHERE status = 'active'
 WITH CHECK OPTION;

この設定により、status が active 以外のデータを挿入・更新しようとするとエラーが発生する。

 -- 成功 (statusが'active'のため)
 INSERT INTO active_users (user_id, username, email, status)
 VALUES (101, 'activeuser', 'active@example.com', 'active');
 
 -- エラー発生 (statusが'active'でないため)
 INSERT INTO active_users (user_id, username, email, status)
 VALUES (100, 'newuser', 'new@example.com', 'inactive');

WITH CHECK OPTIONには、CASCADED と LOCAL の2つのモードがある。

WITH CHECK OPTIONのモード

モード	説明
CASCADED (デフォルト)	全てのネストされたVIEWのWHERE条件をチェックする 基底ビューのWHERE条件も含めてチェックする
LOCAL	現在のVIEWのWHERE条件のみチェックする 下位VIEWは各自のCHECK OPTIONに依存する

• CASCADEDモードの例

   CREATE VIEW base_view AS
   SELECT user_id, username, status
   FROM users
   WHERE status IN ('active', 'pending');
   
   CREATE VIEW active_only AS
   SELECT user_id, username, status
   FROM base_view
   WHERE status = 'active'
   WITH CASCADED CHECK OPTION;
   
   -- エラー発生 (base_viewのWHERE条件も含めてチェックされる)
   INSERT INTO active_only (user_id, username, status)
   VALUES (100, 'user', 'inactive');
  

  
  

• LOCALモードの例

   CREATE VIEW local_view AS
   SELECT user_id, username, status
   FROM users
   WHERE status = 'active'
   WITH LOCAL CHECK OPTION;
   
   -- 現在のVIEWのWHERE条件のみチェックされる
  

マテリアライズドビューの代替手段

MySQLには、マテリアライズドビュー (Materialized View) が存在しない。
マテリアライズドビューは、クエリ結果を実テーブルとして保存し、高速なアクセスを実現する機能である。

MySQLでマテリアライズドビューの代替を実現するには、以下の手順を使用する。

ステップ1 : 集計結果を格納するテーブルを作成

集計結果を格納するテーブルを作成する。

 CREATE TABLE mv_user_summary (
    status VARCHAR(20),
    user_count INT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (status)
 ) ENGINE=InnoDB;

ステップ2 : トリガーまたはイベントスケジューラで更新

トリガーを使用して、基底テーブルの変更時に自動的に更新する。

 DELIMITER $$
 
 CREATE TRIGGER update_user_summary
 AFTER INSERT ON users
 FOR EACH ROW
 BEGIN
    INSERT INTO mv_user_summary (status, user_count)
    VALUES (NEW.status, 1)
    ON DUPLICATE KEY UPDATE user_count = user_count + 1;
 END$$
 
 DELIMITER ;

イベントスケジューラを使用して、定期的に更新する。

 CREATE EVENT refresh_user_summary
 ON SCHEDULE EVERY 1 HOUR
 DO
    REPLACE INTO mv_user_summary (status, user_count)
    SELECT status, COUNT(*)
    FROM users
    GROUP BY status;

ステップ3 : 手動でリフレッシュするストアドプロシージャを作成

手動でリフレッシュするストアドプロシージャを作成する。

 DELIMITER $$
 
 CREATE PROCEDURE refresh_user_summary()
 BEGIN
    TRUNCATE TABLE mv_user_summary;
    INSERT INTO mv_user_summary (status, user_count)
    SELECT status, COUNT(*)
    FROM users
    GROUP BY status;
 END$$
 
 DELIMITER ;
 
 -- 手動でリフレッシュ
 CALL refresh_user_summary();

この手法により、マテリアライズドビューと同等の機能を実現できる。
ただし、データの一貫性とリフレッシュのタイミングには注意が必要である。

VIEWの例

アクセス制御

特定のカラムのみ公開するVIEWを作成し、ユーザにVIEWへのSELECT権限のみ付与する。

以下に示す方法により、機密情報 (パスワード、個人情報等) を隠蔽できる。

 CREATE VIEW public_user_info AS
 SELECT user_id, username, email
 FROM users;
 
 -- 基底テーブルへのアクセスを制限し、VIEWへのアクセスのみ許可
 GRANT SELECT ON database_name.public_user_info TO 'readonly_user'@'localhost';

複雑なクエリの簡素化

複数テーブルのJOINを含む頻繁に使うクエリをVIEW化する。

これにより、アプリケーションコードが簡潔になり、保守性が向上する。

 CREATE VIEW order_summary AS
 SELECT o.order_id, o.order_date, c.customer_name, c.email,
        p.product_name, p.price, o.quantity, p.price * o.quantity AS total
 FROM orders o
 JOIN customers c ON o.customer_id = c.customer_id
 JOIN products p ON o.product_id = p.product_id;
 
 -- 簡素化されたクエリ
 SELECT * FROM order_summary WHERE order_date >= '2024-01-01';

データ抽象化

テーブル構造の変更からアプリケーションを保護する。

これにより、テーブルの正規化や分割を行った場合でも、VIEWを使用することでアプリケーションへの影響を最小限に抑えられる。

 CREATE VIEW user_profile AS
 SELECT user_id, username, email, created_at
 FROM users;
 
 -- テーブル構造が変更されても、VIEWの定義を変更すれば、アプリケーションコードは影響を受けない

計算列の提供

計算値を列として提供するVIEWを作成する。

 CREATE VIEW product_inventory AS
 SELECT product_id, product_name, price, stock,
        price * stock AS inventory_value,
        CASE
           WHEN stock = 0 THEN 'Out of Stock'
           WHEN stock < 10 THEN 'Low Stock'
           ELSE 'In Stock'
        END AS stock_status
 FROM products;

計算ロジックをVIEWに集約することで、アプリケーション側の実装を簡素化できる。

VIEWの制限事項

MySQLのVIEWには、以下に示す制限事項がある。

参照可能なテーブル数

VIEW定義で参照できるテーブルの最大数は61個である。

インデックス作成不可

VIEW自体にはインデックスを作成できない。
ただし、基底テーブルのインデックスは、MERGEアルゴリズムの場合に利用される。

TEMPTABLEアルゴリズムの場合は、基底テーブルのインデックスが利用できないため、パフォーマンスが低下する可能性がある。

ビューのサブクエリ制約

ビューのサブクエリで使用されているテーブルを同時に更新できない。
ただし、TEMPTABLEアルゴリズムを明示的に指定した場合は例外である。

テーブル変更によるビュー無効化

DROP TABLE や ALTER TABLE でVIEWが参照しているテーブルが変更されると、VIEWが無効化される可能性がある。

 CREATE VIEW user_view AS SELECT user_id, username FROM users;
 
 -- カラム名を変更するとVIEWが無効化される
 ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(100);
 
 -- VIEWにアクセスするとエラーが発生する
 SELECT * FROM user_view;  -- エラー: Unknown column 'username'

この場合は、VIEWを再作成する必要がある。

カラム名の長さ制限

カラム名エイリアスの長さ制限は最大64文字である。

SHOW VIEW権限

SHOW CREATE VIEW 文を実行するには、SHOW VIEW 権限が必要である。
権限がない場合は、エラーが発生する。

トリガーの設定不可

VIEWに対してトリガーを設定することはできない。
トリガーは、基底テーブルにのみ設定可能である。

ALGORITHM=MERGEの制約

以下のクエリ要素を含むVIEWは、MERGEアルゴリズムを使用できない。

• 集約関数 (SUM、COUNT、MAX、MIN、AVG)
• DISTINCT
• GROUP BY
• HAVING
• UNION / UNION ALL
• サブクエリ (SELECTリスト内)
• ユーザ定義変数への代入
• LIMITとOFFSETの組み合わせ

この場合、自動的にTEMPTABLEアルゴリズムが使用される。