MochiuWiki : SUSE, EC, PCB

MySQL - VIEW

2026年2月15日 (日) 17:29時点におけるWiki (トーク | 投稿記録)による版 (ページの作成:「== 概要 == MySQLのVIEW (ビュー) は、1つ以上のテーブルから導出される仮想的なテーブルである。<br> VIEWは、SELECT文の結果に名前を付けて保存したもので、実データを持たず、クエリ定義のみを格納する。<br> <br> VIEWを使用することで、複雑なクエリの簡素化、セキュリティの向上、データ抽象化が実現できる。<br> アプリケーションは、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アルゴリズムが使用される。


「https://mochiu.net/index.php?title=MySQL_-_VIEW&oldid=14291」から取得