Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


13.1.20 CREATE VIEW 構文

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

CREATE VIEW ステートメントは、新しいビューを作成するか、または OR REPLACE 句が指定されている場合は既存のビューを置き換えます。そのビューが存在しない場合、CREATE OR REPLACE VIEWCREATE VIEW と同じです。そのビューが存在する場合、CREATE OR REPLACE VIEWALTER VIEW と同じです。

select_statement は、そのビューの定義を提供する SELECT ステートメントです。(ビューから選択すると、事実上、SELECT ステートメントを使用して選択したことになります。) select_statement は、ベーステーブルまたはほかのビューから選択できます。

ビュー定義は作成時に固定されるため、ベースとなるテーブルへのそれ以降の変更はビュー定義に影響を与えません。たとえば、ビューがテーブル上で SELECT * として定義されている場合、あとでテーブルに追加された新しいカラムはそのビューの一部になりません。

ALGORITHM 句は、MySQL によるビューの処理方法に影響を与えます。DEFINER および SQL SECURITY 句は、ビューの呼び出し時にアクセス権限を確認するときに使用されるセキュリティーコンテキストを指定します。WITH CHECK OPTION 句を指定すると、ビューによって参照されているテーブル内の行への挿入または更新を制約できます。これらの句については、このセクションのあとの方で説明されています。

CREATE VIEW ステートメントには、このビューに対する CREATE VIEW 権限と、SELECT ステートメントによって選択される各カラムに対する何らかの権限が必要です。SELECT ステートメント内の別の場所で使用されているカラムに対しては、SELECT 権限が必要です。OR REPLACE 句が存在する場合は、このビューに対する DROP 権限も必要です。このセクションのあとの方で説明されているように、DEFINER 値によっては、CREATE VIEWSUPER 権限も必要になる可能性があります。

ビューが参照されると、このセクションのあとの方で説明されている権限確認が発生します。

ビューはデータベースに属します。デフォルトでは、新しいビューはデフォルトデータベース内に作成されます。ビューを明示的に特定のデータベース内に作成するには、そのビューの作成時に、その名前を db_name.view_name として指定します。

mysql> CREATE VIEW test.v AS SELECT * FROM t;

データベース内で、ベーステーブルとビューは同じ名前空間を共有するため、ベーステーブルとビューが同じ名前を持つことはできません。

SELECT ステートメントによって取得されるカラムは、テーブルカラムへの単純な参照にすることができます。また、関数、定数値、演算子などを使用した式にすることもできます。

ビューは、ベーステーブルと同様に、重複のない一意のカラム名を持つ必要があります。デフォルトでは、SELECT ステートメントによって取得されるカラムの名前はビューカラム名に使用されます。ビューカラムの明示的な名前を定義するには、オプションの column_list 句をカンマで区切られた識別子のリストとして指定できます。column_list 内の名前の数は、SELECT ステートメントによって取得されるカラムの数と同じである必要があります。

SELECT ステートメント内の修飾されていないテーブルまたはビュー名は、デフォルトデータベースを基準にして解釈されます。ビューは、テーブルまたはビュー名を適切なデータベース名で修飾することによって、ほかのデータベース内のテーブルまたはビューを参照できます。

ビューは、多くの種類の SELECT ステートメントから作成できできます。ベーステーブルまたはほかのビューを参照できます。結合、UNION、およびサブクエリーを使用できます。SELECT がテーブルをまったく参照しなくてもかまいません。次の例では、別のテーブルからの 2 つのカラムに加え、それらのカラムから計算される式を選択するビューを定義しています。

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+

ビュー定義は、次の制限に従います。

  • SELECT ステートメントに FROM 句内のサブクエリーを含めることはできません。

  • SELECT ステートメントは、システムまたはユーザー変数を参照できません。

  • ストアドプログラム内で、この定義は、プログラムパラメータまたはローカル変数を参照できません。

  • SELECT ステートメントは、準備済みステートメントのパラメータを参照できません。

  • この定義で参照されているテーブルまたはビューは、すべて存在する必要があります。ただし、ビューが作成されたあとは、この定義で参照されているテーブルまたはビューを削除できます。この場合は、このビューを使用すると、エラーが発生します。この種類の問題に関してビュー定義を確認するには、CHECK TABLE ステートメントを使用します。

  • この定義は TEMPORARY テーブルを参照できないため、TEMPORARY ビューは作成できません。

  • ビュー定義で指定されているテーブルは、すべて定義時に存在する必要があります。

  • トリガーをビューに関連付けることはできません。

  • SELECT ステートメント内のカラム名のエイリアスは (256 文字の別名の最大の長さではなく) 64 文字のカラムの最大の長さに対してチェックされます。

ORDER BY はビュー定義内で許可されていますが、独自の ORDER BY を含むステートメントを使用しているビューから選択した場合は無視されます。

この定義内のその他のオプションまたは句の場合は、そのビューを参照しているステートメントのオプションまたは句に追加されますが、その効果は定義されていません。たとえば、ビュー定義に LIMIT 句が含まれているときに、独自の LIMIT 句を含むステートメントを使用しているビューから選択した場合、どの制限が適用されるかは未定義です。この同じ原則は、SELECT キーワードに続く ALLDISTINCTSQL_SMALL_RESULT などのオプションや、INTOFOR UPDATELOCK IN SHARE MODEPROCEDURE などの句にも適用されます。

ビューを作成したあとに、システム変数の変更によってクエリー処理環境を変更すると、そのビューから得られる結果に影響を与える可能性があります。

mysql> CREATE VIEW v (mycol) AS SELECT 'abc';
Query OK, 0 rows affected (0.01 sec)

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| mycol |
+-------+
1 row in set (0.01 sec)

mysql> SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| abc   |
+-------+
1 row in set (0.00 sec)

DEFINER および SQL SECURITY 句は、そのビューを参照しているステートメントの実行時に、そのビューに対するアクセス権限を確認するときにどの MySQL アカウントを使用するかを決定します。SQL SECURITY 特性の有効な値は、DEFINERINVOKER です。これらは、それぞれ、そのビューを定義したユーザーまたは呼び出したユーザーが必要な権限を持っている必要があることを示します。SQL SECURITY のデフォルト値は DEFINER です。

DEFINER 句に user 値を指定する場合は、'user_name'@'host_name' (GRANT ステートメントで使用されるのと同じ形式)、CURRENT_USER、または CURRENT_USER() として指定された MySQL アカウントにするようにしてください。DEFINER のデフォルト値は、CREATE VIEW ステートメントを実行するユーザーです。これは、明示的に DEFINER = CURRENT_USER を指定するのと同じです。

DEFINER 句を指定した場合は、次のルールによって有効な DEFINER ユーザーの値が決定されます。

  • SUPER 権限がない場合、有効な唯一の user 値は、リテラルで指定するか、または CURRENT_USER を使用して指定した自分のアカウントです。定義者をほかのアカウントに設定することはできません。

  • SUPER 権限がある場合は、構文として有効な任意のアカウント名を指定できます。そのアカウントが実際に存在しない場合は、警告が生成されます。

  • 存在しない DEFINER アカウントでビューを作成することはできますが、SQL SECURITY 値が DEFINER であるが、定義者アカウントが存在しない場合は、そのビューが参照されたときにエラーが発生します。

ビューのセキュリティーの詳細は、セクション20.6「ストアドプログラムおよびビューのアクセスコントロール」を参照してください。

ビュー定義内で、CURRENT_USER は、デフォルトではそのビューの DEFINER 値を返します。SQL SECURITY INVOKER 特性を使用して定義されたビューの場合、CURRENT_USER は、そのビューの呼び出し元のアカウントを返します。ビュー内のユーザー監査については、セクション6.3.13「SQL ベースの MySQL アカウントアクティビティーの監査」を参照してください。

SQL SECURITY DEFINER 特性を使用して定義されたストアドルーチン内で、CURRENT_USER は、そのルーチンの DEFINER 値を返します。ビュー定義に CURRENT_USERDEFINER 値が含まれている場合は、これにより、このようなルーチン内で定義されたビューも影響を受けます。

ビューの権限は、次のように確認されます。

  • ビューの定義時に、ビュー作成者は、そのビューによってアクセスされるトップレベルのオブジェクトを使用するために必要な権限を持っている必要があります。たとえば、ビュー定義がテーブルカラムを参照している場合、作成者は、その定義の選択リスト内の各カラムに対する何らかの権限と、その定義内の別の場所で使用されている各カラムに対する SELECT 権限を持っている必要があります。この定義がストアドファンクションを参照している場合は、その関数を呼び出すために必要な権限のみを確認できます。関数呼び出し時に必要な権限は、その関数が実行されるときにしか確認できません。別の呼び出しでは、その関数内の別の実行パスが選択される可能性があります。

  • ビューを参照するユーザーは、そのビューにアクセスするための適切な権限 (そのビューから選択するための SELECT や、そのビューに挿入するための INSERT など) を持っている必要があります。

  • ビューが参照されると、そのビューによってアクセスされるオブジェクトに対する権限が、SQL SECURITY 特性が DEFINER または INVOKER のどちらであるかに応じて、それぞれ、そのビューの DEFINER アカウントによって保持されている権限または呼び出し元に対して確認されます。

  • ビューへの参照によってストアドファンクションが実行される場合、その関数内で実行されるステートメントの権限確認は、その関数の SQL SECURITY 特性が DEFINER または INVOKER のどちらであるかによって異なります。セキュリティー特性が DEFINER である場合、その関数は DEFINER アカウントの権限で実行されます。この特性が INVOKER である場合、その関数は、そのビューの SQL SECURITY 特性によって決定される権限で実行されます。

例: あるビューがストアドファンクションに依存する可能性があり、さらにその関数がほかのストアドルーチンを呼び出す可能性があります。たとえば、次のビューはストアドファンクション f() を呼び出します。

CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name);

f() に次のようなステートメントが含まれているとします。

IF name IS NULL then
  CALL p1();
ELSE
  CALL p2();
END IF;

f() が実行されるとき、f() 内のステートメントを実行するために必要な権限を確認する必要があります。これは、f() 内の実行パスに応じて、p1() または p2() に対する権限が必要であることを示します。これらの権限は実行時に確認する必要があり、それらの権限を持っている必要のあるユーザーは、ビュー v と関数 f()SQL SECURITY 値によって決定されます。

ビューの DEFINER および SQL SECURITY 句は、標準 SQL への拡張です。標準 SQL では、ビューは SQL SECURITY DEFINER のルールを使用して処理されます。標準には、ビューの定義者 (これは、ビューのスキーマの所有者と同じです) はそのビューに対する該当する権限 (SELECT など) を取得し、またそれらを付与することができると記載されています。MySQL にはスキーマの所有者という概念がないため、MySQL では定義者を識別するための句が追加されています。DEFINER 句は、標準が備えている機能、つまり、だれがそのビューを定義したかについての永続的なレコードを備えることを目的とした拡張です。DEFINER のデフォルト値がビュー作成者のアカウントになっているのはそのためです。

オプションの ALGORITHM 句は、標準 SQL への MySQL 拡張です。これは、MySQL によるビューの処理方法に影響を与えます。ALGORITHM は、MERGETEMPTABLE、または UNDEFINED の 3 つの値を受け取ります。ALGORITHM 句が存在しない場合、デフォルトのアルゴリズムは UNDEFINED です。詳細は、セクション20.5.2「ビュー処理アルゴリズム」を参照してください。

いくつかのビューは更新可能です。つまり、これらのビューを UPDATEDELETEINSERT などのステートメントで使用して、ベースとなるテーブルの内容を更新できます。ビューが更新可能であるためには、そのビュー内の行とベースとなるテーブル内の行の間に 1 対 1 の関係が存在する必要があります。また、ビューを更新不可能にするその他の特定の構造構文も存在します。

更新可能なビューに対して WITH CHECK OPTION 句を指定すると、select_statement 内の WHERE 句が true である行を除く行への挿入または更新を回避できます。

更新可能なビューに対する WITH CHECK OPTION 句では、そのビューが別のビューとの関連で定義されている場合、LOCAL および CASCADED キーワードによってチェックテストのスコープが決定されます。LOCAL キーワードは、CHECK OPTION を、定義されているビューのみに制限します。CASCADED を指定すると、ベースとなるビューに対するチェックも評価されます。どちらのキーワードも指定されていない場合、デフォルトは CASCADED になります。

更新可能なビューおよび WITH CHECK OPTION 句の詳細は、セクション20.5.3「更新可能および挿入可能なビュー」を参照してください。


User Comments
  Posted by Olexandr Melnyk on June 19, 2007
A simple workaround for MySQL's limitation on local variables usage in views is to use a function, which returns variable's value:

create function book_subject
returns varchar(64) as
return @book_subject;

create view thematical_books as
select title
, author
from books
where subject = book_subject();
  Posted by Tim Champion on August 31, 2007
I ran into the restriction where your view cannot contain a subquery in the FROM clause. My work around was to make my subquery a view instead and then join my table with the view.

I tried:
create view v1 as select * from t1 JOIN (select * from t2 where ...) USING (id) where ..
which gave me the "subquery in FROM clause" error.

Solution/work around:
create view v2 as select * from t2 where ...;
create view v1 as select * from t1 JOIN v2 USING (id) where ...

(example above is an overly simplified version of what I did for demonstration puposes).
  Posted by Rafael Palacios on November 14, 2007
Simulating Materialized View
Materialized views are not updated every time they are accessed; they behave as static tables hence are much faster than normal view. Materialized views are useful when the system performs lots of queries on the view while the original data changes infrequently.

Since MySQL currently doesn't support materialized views, here is simple way to simulate materialized views that consists on creating a static table and a robust updating script.

The tipical command for creating a normal view is:
CREATE VIEW my_view AS SELECT <xxxxxx your select expression xxxxx>

In order to create the equivalent to a materialized view you create a table with the same name instead of a view.
Then you create the following MySQL command that can be run periodically, for example every night.

CREATE VIEW my_view AS SELECT <*** your select expression ***>

In order to create the equivalent to a materialized view you create a table with the same name instead of a view.
Then you create the following MySQL command that can be run periodically, for example every night.

CREATE TEMPORARY TABLE tmp_my_view SELECT <*** same select expression as before ***>

LOCK TABLE my_database.my_view WRITE;
DELETE FROM my_database.my_view;
INSERT INTO my_database.my_view SELECT * FROM tmp_my_view;
UNLOCK TABLES;

The previous MySQL script can be run in Unix/Linux system by adding the following command as a cron entry:
mysql -u userid --password=XXXXX -D my_database < update_materialized_view.mysql >/dev/null 2>&1

  Posted by Justin Rennell on July 2, 2009
I was able to simulate a materialized view off of a regular view by executing a simpler script than above.
With any regular view, "myRegularView":

DROP TABLE IF EXISTS `myDatabase`.`myMaterializedView`;
CREATE TABLE `myDatabase`.`myMaterializedView` SELECT * from `myDatabase`.`myRegularView`;

Running this script at whatever interval is appropriate will create a new regular table with the contents of the view at the time of execution.
  Posted by Aaron Tavistock on July 9, 2009
> DROP TABLE IF EXISTS `myDatabase`.`myMaterializedView`;
> CREATE TABLE `myDatabase`.`myMaterializedView` SELECT * from `myDatabase`.`myRegularView`;

If you do this without locking the table you risk performing CRUD operations against the table while its missing, which will throw errors or show empty results when something is legitimately there. Obviously simply locking the table won't work, because a DROP TABLE will also drop the lock. Since RENAME TABLE locks all the tables involved and is typically a very quick operation, making a working table then using RENAME TABLE to swap the tables will work and remain functional throughout.

For example:

CREATE TABLE new_materialized_view SELECT * from regular_view;
RENAME TABLE materialized_view = old_materialized_view, new_materialized_view = materialized_view;
DROP TABLE IF EXISTS old_materialized_view;

  Posted by Ravishankar Narayana on October 13, 2010
Keep in mind there is problem with using group by with roll up in views that select using a join on 2 or more tables.

This is reported here..

http://bugs.mysql.com/bug.php?id=47693
  Posted by Manolo Guerrero on October 14, 2010
Creating a materialized view emulation as described above looks good, the only problem is that we are inheriting the lack of indexes MySQL views expose.

My solution is to create a correctly indexed table according to my needs, having the exact same structure as the view, and then running something like this:

LOCK TABLES materializedView WRITE;
TRUNCATE materializedView;
INSERT INTO materializedView SELECT * FROM regularView;
UNLOCK TABLES;

That way all indexes from materializedView are preserved on every "refresh".

I'm planning to use this in an application I'm doing right now, where we will have a lot more SELECTs than inserts/updates. If I keep a regular view for my SELECTs, I'll be asking the server to make tons of calculations every time someone needs to know how many items are on stock for product "A", instead, I'll have all SELECTs towards the "materializedView" with correct SKU, Store and Period indexes.

The view "refresh" will occur every time someone runs an INSERT or UPDATE, which will be on a 20 to 1 ratio. (20 Selects for every Update or Insert)

I hope things go as smooth as I'm planning. Greetings ;-)
  Posted by Clifford Janson on November 26, 2013
There is a way to maintain a materialized view without having to do periodic refreshes, by deploying triggers that instantly update the view whenever the underlying table changes. See: http://www.materialized.info
Sign Up Login You must be logged in to post a comment.