This topic describes how to execute the CREATE VIEW
statement to create a view.
Usage notes
- Versions earlier than 3.1.9.0
AnalyticDB for MySQL is not compatible with the default behavior of MySQL. If a column is added to or removed from your view, the system identifies the change in the number of columns from the response of the
SELECT * FROM <view_name>;
statement, determines the view to be unavailable, and then reports the following error:View '<view_name>' is stale; it must be re-created
. - V3.1.9.0 and later
AnalyticDB for MySQL is compatible with the default behavior of MySQL. When you use the CREATE VIEW statement to create a view, AnalyticDB for MySQL saves the statement after parsing the asterisk (
*
) into specific columns. In this case, no error occurs when you add or remove a column.
*
) in the CREATE VIEW statement. The compatibility of AnalyticDB for MySQL clusters of V3.1.9.0 or later with MySQL behavior may cause special impacts. For example, when you rename column C as column D, the system fails to find column C when it references column A, column B, and column C
, and then reports an error. This is an expected result because the parsing stage that contains SQL syntax check and authentication is earlier than the optimization stage in which columns are removed. The system reports an error in the parsing stage regardless of whether column C is used for query. In AnalyticDB for MySQL of versions earlier than 3.1.9.0, the system only checks whether the number of columns in a view is the same as the number of columns that are referenced by the asterisk (*
). In this case, no errors are reported, and the system maps column C to the third column of the base table. When you query column C after the RENAME operation, no errors are reported. This is an unexpected result.
- Add the following hint to the CREATE VIEW statement for a single view:
/*+LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE=false*/
. Sample statement:/*+LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE=false*/ CREATE VIEW v0 AS SELECT * FROM base0;
- Execute the
SET ADB_CONFIG LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE = false;
statement for all views.
Syntax
CREATE
[OR REPLACE]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name
AS select_statement;
Option | Required | Description |
---|---|---|
OR REPLACE | No | Creates a view based on whether an existing view uses the same name. The following rules apply:
Note If this option is not specified, no view can be created when an existing view uses the same name. |
[SQL SECURITY] | Defines the method for security authentication when data is queried from a view. Valid values:
Note
| |
view_name | Yes | The name of the view. Note When you name a view, you can also add the name of the database that contains the view before the name of the view. Example: adb_demo.view . If you do not specify a database, the view is created in the current database. |
select_statement | The data source of the view. |
Examples
- Prepare data
Perform the following operations by using the privileged account of an AnalyticDB for MySQL cluster:
- Create an account named
user1
.CREATE USER user1 IDENTIFIED BY 'user1_pwd';
- Create a table named
t1
in an existing database namedadb_demo
.Create Table `t1` ( `id` bigint AUTO_INCREMENT, `id_province` bigint NOT NULL, `user_info` varchar, primary key (`id`) ) DISTRIBUTE BY HASH(`id`);
Insert the test data into thet1
table.INSERT INTO t1(id_province,user_info) VALUES (1,'Tom'),(1,'Jerry'),(2,'Jerry'),(3,'Mark');
- Create an account named
- Create viewsNote These examples demonstrate how to specify different authentication methods when you create views. The data in the views comes from the
t1
table.- When you create a view named
v1
, setSQL SECURITY
toINVOKER
.CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
- When you create a view named
v2
, setSQL SECURITY
toDEFINER
.CREATE SQL SECURITY DEFINER VIEW v2 AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
- When you create a view named
v3
, do not specify theSQL SECURITY
option. In this case,INVOKER
is used.CREATE VIEW v3 AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
- When you create a view named
- Compare permissions
- Use the privileged account to grant the
user1
account the permissions to query the three views.GRANT SELECT ON adb_demo.v1 TO 'user1'@'%'; GRANT SELECT ON adb_demo.v2 TO 'user1'@'%'; GRANT SELECT ON adb_demo.v3 TO 'user1'@'%';
In this case, after you use theuser1
account to connect to theadb_demo
database of the AnalyticDB for MySQL cluster, you can use theuser1
account to query data only from thev2
view.
The following result is returned:SELECT * FROM v2
+-------------+-----------+ | ID_PROVINCE | USER_INFO | +-------------+-----------+ | 1 | Tom | | 1 | Jerry | +-------------+-----------+
When you query data from thev1
orv3
view by using the SELECT statement, an error is returned.
orSELECT * FROM v1
SELECT * FROM v3
The following error message is returned when you execute one of the preceding SELECT statements:ERROR 1815 (HY000): [20049, 2021083110261019216818804803453927668] : Failed analyzing stored view
- After the
user1
account is granted the permissions to query the three views, use the privileged account to grant theuser1
account the permissions to query thet1
table.GRANT SELECT ON adb_demo.t1 to user1@'%';
In this case, after you use theuser1
account to connect to theadb_demo
database of the AnalyticDB for MySQL cluster, you can use theuser1
account to query data from thev1
,v2
, andv3
views.
orSELECT * FROM v1
orSELECT * FROM v2
SELECT * FROM v3
The following result is returned when you execute one of the preceding SELECT statements:+-------------+-----------+ | ID_PROVINCE | USER_INFO | +-------------+-----------+ | 1 | Tom | | 1 | Jerry | +-------------+-----------+
- Use the privileged account to grant the
Best practices
For more information, see Permission management by using views .