ビューの作成 - AnalyticDB for MySQL - Alibaba Cloud ドキュメントセンター

このトピックでは、CREATE VIEWステートメントを実行してビューを作成する方法について説明します。

使用上の注意

AnalyticDB for MySQLとMySQL間のビューの互換性:

3.1.9.0より前のバージョン
AnalyticDB for MySQLはMySQLのデフォルト動作と互換性がありません。ビューに列が追加または削除された場合、システムはSELECT * from <view_name>; ステートメントの応答から列数の変更を識別し、ビューを使用できないと判断し、次のエラーを報告します。view '<view_name>' is stale; it must be recreated.
V3.1.9.0以降
AnalyticDB for MySQLは、MySQLのデフォルト動作と互換性があります。 CREATE VIEWステートメントを使用してビューを作成すると、AnalyticDB for MySQLは、ステートメント内のアスタリスク (*) を解析した後のステートメントを特定の列に保存します。この場合、列を追加または削除するときにエラーは発生しません。

予期しないエラーを防ぐため、V3.1.9.0以降のAnalyticDB for MySQLクラスターを使用することを推奨します。 CREATE VIEWステートメントでは、特定の列名を使用することを推奨しますが、アスタリスク (*) は使用しないことを推奨します。

説明

クラスターのマイナーバージョンをクエリする方法については、AnalyticDB for MySQLクラスターのバージョンを照会するにはどうすればよいですか? クラスターのマイナーバージョンを更新するには、テクニカルサポートにお問い合わせください。

V3.1.9.0以降のAnalyticDB for MySQLクラスターとMySQLの動作との互換性は、特別な影響を与える可能性があります。たとえば、列Cの名前を列Dに変更すると、列A、列B、および列Cを参照しているときに列Cが見つからず、エラーが報告されます。これは、SQL構文のチェックと認証が実行される解析ステージが、列が削除される最適化ステージよりも早いため、期待される結果です。列Cがクエリに使用されているかどうかに関係なく、解析段階でエラーが報告されます。 3.1.9.0より前のバージョンのAnalyticDB for MySQLでは、ビュー内の列数がアスタリスク (*) で参照されている列数と同じかどうかのみがチェックされます。この場合、エラーは報告されず、システムは列Cをベーステーブルの3番目の列にマッピングします。 RENAME操作後に列Cをクエリしても、エラーは報告されません。これは予想外の結果である。

3.1.9.0より前のバージョンのAnalyticDB for MySQLクラスターの特別な動作との互換性が必要な場合は、CREATE VIEWステートメントにヒントを追加するか、特定のグローバルパラメーターを設定できます。

1つのビューのCREATE VIEWステートメントに、/* + LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE=false */ のヒントを追加します。例：
```
/*+LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE=false*/
CREATE VIEW v0
AS
SELECT * FROM base0;
```
すべてのビューに必要な設定を適用するには、次のグローバルパラメーターを設定します。SET ADB_CONFIG LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE = false;

構文

CREATE
[OR REPLACE]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name
AS select_statement;

オプション	必須	説明
`または交換`	任意	既存のビューが同じ名前を使用するかどうかに基づいてビューを作成します。次のルールが適用されます。同じ名前を使用する既存のビューがない場合、AnalyticDB for MySQLはビューを作成します。既存のビューが同じ名前を使用する場合、AnalyticDB for MySQLは既存のビューを削除し、ビューを作成します。説明このオプションを指定しない場合、既存のビューが同じ名前を使用している場合、ビューは作成できません。
`[SQLセキュリティ]`	任意	ビューからデータを照会するときのセキュリティ認証の方法を定義します。有効な値： `INVOKER`: `呼び出し元`の権限を使用してSQLステートメントを実行します。この認証方法を使用すると、ビューからデータを照会するときに、インボーカーに次の権限があるかどうかをシステムで確認できます。ビューを照会する権限ビューによって参照されるオブジェクトを照会する権限前述の権限を持つインボーカーだけが、ビューからデータをクエリできます。 `DEFINER`: `definer`の権限を使用してSQL文を実行します。この認証方法を使用すると、ビューからデータを照会するときに、インボーカと定義者が次の権限を持っているかどうかをシステムで確認できます。ビューをクエリする権限 (インボーカーによる) ビューによって参照されるオブジェクトを照会する権限 (定義者による) 定義者の権限が取り消されると、インボーカーがビューを照会する権限を持っていても、ビューからデータを照会することはできません。説明このオプションを指定しない場合、AnalyticDB for MySQLのデフォルトの認証方法として`INVOKER`が使用されます。インボーカーがビューからデータを照会する場合、インボーカーにはビューとビューによって参照されるオブジェクトを照会する権限が必要です。このオプションは、V3.1.4.0以降のAnalyticDB for MySQLクラスターで使用できます。 AnalyticDB For MySQLクラスターのマイナーバージョンを照会する方法については、AnalyticDB for MySQLクラスターのバージョンを照会するにはどうすればよいですか? クラスターのマイナーバージョンを更新するには、テクニカルサポートにお問い合わせください。
`view_name`	可	ビューの名前。説明ビューに名前を付けるときに、ビューの名前の前にビューを含むデータベースの名前を追加することもできます。例: `adb_demo.view` データベースを指定しない場合、ビューは現在のデータベースに作成されます。
`select_statement`	可	ビューのデータソース。

例

データの準備
AnalyticDB for MySQLクラスターの特権アカウントを使用して、次の操作を実行します。
1. user1という名前のアカウントを作成します。
```
CREATE USER user1 IDENTIFIED BY 'user1_pwd';
```
2. adb_demoという名前の既存のデータベースにt1という名前のテーブルを作成します。
```
Create Table `t1` (
 `id` bigint AUTO_INCREMENT,
 `id_province` bigint NOT NULL,
 `user_info` varchar,
 primary key (`id`)
) DISTRIBUTE BY HASH(`id`);
```
  テストデータをt1テーブルに挿入します。
```
INSERT INTO t1(id_province,user_info) VALUES (1,'Tom'),(1,'Jerry'),(2,'Jerry'),(3,'Mark');
```
ビューの作成
説明
次の例は、ビューを作成するときに異なる認証方法を指定する方法を示しています。ビューのデータはt1テーブルから取得されます。
- SQLセキュリティをINVOKERに設定して、v1という名前のビューを作成します。
```
CREATE SQL SECURITY INVOKER VIEW v1 
  AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
```
- SQLセキュリティをDEFINERに設定して、v2という名前のビューを作成します。
```
CREATE SQL SECURITY DEFINER VIEW v2 
  AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
```
- v3という名前のビューを作成し、SQL SECURITYオプションを指定しないでください。この場合、INVOKER認証方式が使用されます。
```
CREATE VIEW v3 
  AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
```
アクセス許可の比較
- 特権アカウントを使用して、user1アカウントに3つのビューを照会する権限を付与します。
```
GRANT SELECT ON adb_demo.v1 TO 'user1'@'%';
GRANT SELECT ON adb_demo.v2 TO 'user1'@'%';
GRANT SELECT ON adb_demo.v3 TO 'user1'@'%';
```
  この場合、user1アカウントを使用してAnalyticDB for MySQLクラスターのadb_demoデータベースに接続した後、user1アカウントを使用してv2ビューからのみデータを照会できます。
```
SELECT * FROM v2
```
  次の応答が返されます。
```
+-------------+-----------+
| ID_PROVINCE | USER_INFO |
+-------------+-----------+
|           1 | Tom       |
|           1 | Jerry     |
+-------------+-----------+
```
  SELECTステートメントを使用してv1またはv3ビューからデータをクエリすると、エラーが返されます。
```
SELECT * FROM v1
```
  または
```
SELECT * FROM v3
```
  前述のSELECTステートメントのいずれかを実行すると、次のエラーメッセージが返されます。
```
ERROR 1815 (HY000): [20049, 2021083110261019216818804803453927668] : Failed analyzing stored view
```
- user1アカウントに3つのビューを照会する権限が付与されたら、特権アカウントを使用してuser1アカウントにt1テーブルを照会する権限を付与します。
```
GRANT SELECT ON adb_demo.t1 to user1@'%';
```
  この場合、user1アカウントを使用してAnalyticDB for MySQLクラスターのadb_demoデータベースに接続した後、user1アカウントを使用してv1、v2、およびv3ビューのデータを照会できます。
```
SELECT * FROM v1
```
  または
```
SELECT * FROM v2
```
  または
```
SELECT * FROM v3
```
  上記のSELECTステートメントのいずれかを実行すると、次の結果が返されます。
```
+-------------+-----------+
| ID_PROVINCE | USER_INFO |
+-------------+-----------+
|           1 | Tom       |
|           1 | Jerry     |
+-------------+-----------+
```

ベストプラクティス

詳細については、「ビューを使用した権限管理」をご参照ください。