技術資料

MySQLでテーブルを扱っていると、ビュー（VIEW）という名前を見かけることがあります。SELECTで参照できる点は似ていますが、テーブルとビューでは役割が異なります。

テーブルはデータを保存する場所、ビューはSELECTの定義に名前を付け、テーブルのように参照できるようにしたもの、と考えると分かりやすいです。よく使う抽出条件や集計をビューにしておくと、呼び出し側で同じSQLを何度も書かずに済みます。

今回は、基本的な違いと、架空の注文データを抽出・集計する例を説明します。SELECTの基本が分かる人向けで、対象はMySQL 8.0系、操作するクライアントはWorkbenchを想定しています。

テーブルとビューの違い

MySQLの通常のビューは、作成時点の検索結果を別の保存先へコピーするものではありません。参照するたびに、定義したSELECTに基づいて元データから結果を取得します。重い集計をビューにしても、その計算結果が保存されて、次回から集計を省略できるわけではありません。

ただし、内部ではビューのSELECTを呼び出し側のSQLへ組み込むなどの最適化が行われるため、ビューのSELECTが必ず独立したSQLとして実行される、という意味ではありません。処理方式については後半で補足します。

例えば「完了した注文」を取得するビューなら、参照する時点で、そのSELECTとトランザクションの条件に合う注文が取得されます。後から参照しても作成時点の結果に固定される、という仕組みではないわけです。

このため、ビューを作ることと、テーブルをコピーしてバックアップを取ることは別です。概要は、MySQL公式のビューの説明も参照してください。

どんなときにビューを使うか

例えば、システムに組み込む処理なら、アプリケーションのコード側でSQLを共通化できます。既に一つの関数やメソッドにまとめられていれば、何度も実行するという理由だけでビューを作る必要はありません。

一方、運用・保守の文脈でWorkbenchなどから同じSELECTを頻繁に実行するなら、ビューを用意しておくと便利です。「完了した注文の一覧」や「顧客ごとの注文金額」のような抽出・集計を、長いSQLを毎回書かずに名前で参照できます。

ただし、システム内では使わない、という区分ではありません。複数のシステムや帳票ツールで同じ抽出条件を共有したい場合にも、ビューが候補になります。SELECTの定義をアプリケーション側で管理するか、DB側にまとめて共有するか、と考えると使い分けやすいかと思います。

また、元テーブルを直接参照する権限は持たせず、見せてよい列や行だけをビューで提供する使い方もあります。例えば、顧客テーブルの氏名やメールアドレスは見せず、顧客IDと地域だけを参照できるようにする、といった用途です。

この場合は、SQL SECURITY DEFINERのビューと権限設定を組み合わせます。利用者にはビューへのSELECT権限を付与し、元テーブルへの直接の参照権限は付与しません。ビューで列や行を絞るだけでは、既にある参照権限を制限できないためです。権限の考え方は後半で補足します。

CREATE VIEWの基本

基本的な形は、CREATE VIEWの後にビュー名を書き、ASの後ろにSELECTを続けます。

CREATE VIEW view_name AS
SELECT column_name
FROM table_name;

SELECT column_name
FROM view_name;

先に元のテーブルを用意し、その列を使ってビューを定義します。作成後は、FROM句にビューの名前を指定して参照できます。

ビューはDB内のオブジェクトとして作成され、接続を閉じた後も定義が残ります。一つのSQL文の中だけで使うCTEとは、この点が異なります。CTEについては、MySQLのWITH句入門で説明しています。

ビューにはCREATE VIEW権限が必要です。また、同じDB内ではテーブルとビューに同じ名前を付けられません。詳しい構文と条件は、MySQL公式のCREATE VIEWの説明を確認してください。

注文データから必要な行と列を取り出す

ここからは、検証用DBに架空の注文テーブルを作る例です。Workbenchで接続先と使用するDBを確認し、view_demo_orders、view_demo_completed_orders、view_demo_customer_totalsという名前が未使用であることを確認してください。SQLは、前の操作が成功したことを確認しながら順に実行します。接続やSQL実行の基本操作は、Workbench入門の記事も参照してください。

SELECT VERSION(), DATABASE();

CREATE TABLE view_demo_orders (
    id INT UNSIGNED NOT NULL,
    customer_id INT UNSIGNED NOT NULL,
    status VARCHAR(20) NOT NULL,
    amount INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DATABASE()がNULLならDBが選ばれていません。Workbenchで検証用DBを選んでから、CREATE TABLEを実行します。amountは円単位の金額を整数で保存する列です。続けて、4件のデータを追加します。このINSERTは一度だけ実行してください。

INSERT INTO view_demo_orders (id, customer_id, status, amount)
VALUES
    (1, 101, 'completed', 1500),
    (2, 101, 'pending',    800),
    (3, 102, 'completed', 2200),
    (4, 101, 'completed',  500);

statusがcompletedの行を完了した注文として扱います。必要な行と列を取り出すだけなら、普通にSELECTを書けばOKです。

SELECT id, customer_id, amount
FROM view_demo_orders
WHERE status = 'completed'
ORDER BY id;

期待結果は次の3行です。

同じ抽出条件をビューにする

この条件を繰り返し使うなら、SELECTをビューとして定義できます。

CREATE SQL SECURITY INVOKER VIEW view_demo_completed_orders AS
SELECT id, customer_id, amount
FROM view_demo_orders
WHERE status = 'completed';

SQL SECURITY INVOKERは、元テーブルへアクセスする際に、ビューを利用するユーザーの権限で確認する指定です。権限の違いは後ほど補足します。ここでは、ビューを作成するユーザーも利用するユーザーも、元テーブルの対象列にSELECT権限を持つ前提です。

作成後は、次のように取得できます。

SELECT id, customer_id, amount
FROM view_demo_completed_orders
ORDER BY id;

期待結果は先ほどと同じ3行です。利用する側では、statusの条件を書かずに「完了した注文」を参照できます。並び順は取得するSELECT側で指定しています。

もちろん、一度しか使わない短いSELECTなら、そのまま書いても構いません。同じ条件を複数のSQLで使う、条件の定義をまとめて管理したい、といった場面でビューが候補になります。

顧客ごとの集計結果を参照する

ビューには、GROUP BYを使った集計も定義できます。例えば、完了した注文の件数と合計金額を顧客ごとに参照するなら、次のように書けます。

CREATE SQL SECURITY INVOKER VIEW view_demo_customer_totals AS
SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM view_demo_orders
WHERE status = 'completed'
GROUP BY customer_id;

参照する側は、ビューの列を指定するだけです。

SELECT customer_id, order_count, total_amount
FROM view_demo_customer_totals
ORDER BY customer_id;

期待結果です。

抽出用のビューは注文を1件ずつ返し、集計用のビューは顧客ごとの結果を返します。どちらもSELECTで参照できますが、1行が何を表すかは定義によって変わります。

元データを変えるとどうなるか

検証用の元テーブルで、保留中だったidが2の注文を完了に変更してみます。このUPDATEは、先ほど作成した説明用テーブルだけを対象にします。

START TRANSACTION;

UPDATE view_demo_orders
SET status = 'completed'
WHERE id = 2;

SELECT id, customer_id, amount
FROM view_demo_completed_orders
ORDER BY id;

SELECT customer_id, order_count, total_amount
FROM view_demo_customer_totals
ORDER BY customer_id;

ROLLBACK;

同じ接続でこの順序で実行した場合、ROLLBACK前の抽出結果にはidが2の行が加わり、4行になる想定です。集計結果の期待値は次のようになります。

ビューを作り直さなくても、元の行に対する変更が取得結果に現れます。最後のROLLBACKでこのUPDATEを取り消し、もう一度SELECTすると、変更前の3行と集計値に戻る想定です。作成済みのテーブル・ビューと、最初に追加した4件のデータは残ります。途中でエラーが出て実行が止まった場合も、同じ接続でROLLBACKしてから状態を確認してください。

なお、別の接続からの変更がいつ見えるかは、COMMITやトランザクションの分離レベルなどで変わります。ビューだから未コミットの変更まで見える、ということではありません。取消操作はMySQL公式のトランザクション制御の説明、参照できるデータの範囲はInnoDBの一貫性読み取りの説明で確認できます。

ビューを使うときの注意点

すべてのビューを更新できるわけではない

条件を満たすビューにはUPDATEなどを実行できます。その場合に変わるのは、ビュー専用のコピーではなく、元テーブルのデータです。

ただし、今回の集計用ビューはSUM、COUNT、GROUP BYを使っているため、更新できません。顧客ごとにまとめた合計を変更しても、元のどの注文をどう変えるべきか一意には決められない、と考えると分かりやすいかと思います。条件の詳細は、MySQL公式の更新可能なビューの説明を参照してください。

集計結果を保存して高速化する仕組みとは異なる

ビューにして呼び出し側のSQLが短くなっても、DBの処理が軽くなるとは限りません。MySQLは、ビューの定義を呼び出し側のSQLへ組み込んだり、処理中に一時テーブルを使ったりします。この一時テーブルは、作成時の集計結果を永続保存して使い回すという意味ではありません。

詳しくは、MySQL公式のビューの処理方式を確認してください。性能が目的なら、SQLの見た目だけで判断せず、EXPLAINや実際の処理時間で確認します。

参照する権限と、元テーブルへアクセスする権限を確認する

ビューからSELECTするには、ビュー自体へのSELECT権限が必要です。さらに元テーブルへの権限を誰のものとして確認するかは、SQL SECURITYで変わります。

• INVOKER：ビューを利用するユーザーの権限で確認します。今回のサンプルはこちらです。
• DEFINER：ビューの定義者として指定されたアカウントの権限で確認します。SQL SECURITYを省略した場合の既定値です。

INVOKERを指定した場合、利用者自身に、元テーブルの必要な列を参照するSELECT権限が必要です。ビューを作っても、利用者が既に持っている元テーブルへの参照権限は制限されません。

「元テーブルを直接参照させず、ビューで選んだ列だけを見せたい」という場合は、DEFINERの利用と、元テーブル・ビューそれぞれへの権限付与を検討すると良いでしょう。詳しくは、MySQL公式の格納オブジェクトのアクセス制御を参照してください。

また、ビューが参照する列やテーブルを削除すると、参照時にエラーになる場合があります。元テーブルの構造を変更するときは、ビューへの影響も確認しましょう。

行データを保存するならテーブル、繰り返し使うSELECTに名前を付けて参照するならビュー。まずは抽出や集計の小さな例で、元のSELECTと同じ結果になることを比べてみると、使いどころをつかみやすいかと思います。