Learn to use views in MariaDB Server. This section explains how to create virtual tables from query results, simplifying complex queries and enhancing data security and abstraction.
ALTER
[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]This statement changes the definition of a view, which must exist. The
syntax is similar to that for CREATE VIEW and the effect is the same
as for CREATE OR REPLACE VIEW if the view exists. This statement
requires the CREATE VIEW and DROP privileges for the view, and some
privilege for each column referred to in the SELECT statement. ALTER VIEW is allowed only to the definer or users with the SUPER privilege.
ALTER VIEW v AS SELECT a, a*3 AS a2 FROM t;This page is licensed: GPLv2, originally from fill_help_tables.sql
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW [IF NOT EXISTS] view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]The CREATE VIEW statement creates a new view, or replaces an existing one if the OR REPLACE clause is given. If the view does not exist, CREATE OR REPLACE VIEW is the same as CREATE VIEW. If the view does exist, CREATE OR REPLACE VIEW is the same as ALTER VIEW.
The select_statement is a SELECT statement that provides the definition of the view. (When you select from the view, you select in effect using the SELECT statement.) select_statement can select from base tables or other views.
The view definition is "frozen" at creation time, so changes to the underlying tables afterwards do not affect the view definition. For example, if a view is defined as SELECT * on a table, new columns added to the table later do not become part of the view. A SHOW CREATE VIEW shows that such queries are rewritten and column names are included in the view definition.
The view definition must be a query that does not return errors at view creation times. However, the base tables used by the views might be altered later and the query may not be valid anymore. In this case, querying the view will result in an error. CHECK TABLE helps in finding this kind of problems.
The ALGORITHM clause affects how MariaDB processes the view. The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at view invocation time. The WITH CHECK OPTION clause can be given to constrain inserts or updates to rows in tables referenced by the view. These clauses are described later in this section.
The CREATE VIEW statement requires the CREATE VIEW privilege for the view, and some privilege for each column selected by the SELECT statement. For columns used elsewhere in the SELECT statement you must have the SELECT privilege. If the OR REPLACE clause is present, you must also have the DROP privilege for the view.
A view belongs to a database. By default, a new view is created in the default database. To create the view explicitly in a given database, specify the name as db_name.view_name when you create it.
CREATE VIEW test.v AS SELECT * FROM t;Base tables and views share the same namespace within a database, so a database cannot contain a base table and a view that have the same name.
Views must have unique column names with no duplicates, just like base tables. By default, the names of the columns retrieved by the SELECT statement are used for the view column names. To define explicit names for the view columns, the optional column_list clause can be given as a list of comma-separated identifiers. The number of names in column_list must be the same as the number of columns retrieved by the SELECT statement.
Columns retrieved by the SELECT statement can be simple references to table columns. They can also be expressions that use functions, constant values, operators, and so forth.
Unqualified table or view names in the SELECT statement are interpreted with respect to the default database. A view can refer to tables or views in other databases by qualifying the table or view name with the proper database name.
A view can be created from many kinds of SELECT statements. It can refer to base tables or other views. It can use joins, UNION, and subqueries. The SELECT need not even refer to any tables. The following example defines a view that selects two columns from another table, as well as an expression calculated from those columns:
CREATE TABLE t (qty INT, price INT);
INSERT INTO t VALUES(3, 50);
CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
SELECT * FROM v;
+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 3 | 50 | 150 |
+------+-------+-------+A view definition is subject to the following restrictions:
The SELECT statement cannot contain a subquery in the FROM clause.
The SELECT statement cannot refer to system or user variables.
Within a stored program, the definition cannot refer to program parameters or local variables.
The SELECT statement cannot refer to prepared statement parameters.
Any table or view referred to in the definition must exist. However, after a view has been created, it is possible to drop a table or view that the definition refers to. In this case, use of the view results in an error. To check a view definition for problems of this kind, use the CHECK TABLE statement.
The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view.
Any tables named in the view definition must exist at definition time.
You cannot associate a trigger with a view.
For valid identifiers to use as view names, see Identifier Names.
ORDER BY is allowed in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY.
For other options or clauses in the definition, they are added to the options or clauses of the statement that references the view, but the effect is undefined. For example, if a view definition includes a LIMIT clause, and you select from the view using a statement that has its own LIMIT clause, it is undefined which limit applies. This same principle applies to options such as ALL, DISTINCT, or SQL_SMALL_RESULT that follow the SELECT keyword, and to clauses such as INTO, FOR UPDATE, and LOCK IN SHARE MODE.
The PROCEDURE clause cannot be used in a view definition, and it cannot be used if a view is referenced in the FROM clause.
If you create a view and then change the query processing environment by changing system variables, that may affect the results that you get from the view:
CREATE VIEW v (mycol) AS SELECT 'abc';
SET sql_mode = '';
SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| mycol |
+-------+
SET sql_mode = 'ANSI_QUOTES';
SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| abc |
+-------+The DEFINER and SQL SECURITY clauses determine which MariaDB account to use when checking access privileges for the view when a statement is executed that references the view. They were added in MySQL 5.1.2. The legal SQL SECURITY characteristic values are DEFINER and INVOKER. These indicate that the required privileges must be held by the user who defined or invoked the view, respectively. The default SQL SECURITY value is DEFINER.
If a user value is given for the DEFINER clause, it should be a MariaDB account in 'user_name'@'host_name' format (the same format used in the GRANT statement). The user_name and host_name values both are required. The definer can also be given as CURRENT_USER or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE VIEW statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.
If you specify the DEFINER clause, these rules determine the legal DEFINER user values:
If you do not have the SUPER privilege, or, from MariaDB 10.5.2, the SET USER privilege, the only legal user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account.
If you have the SUPER privilege, or, from MariaDB 10.5.2, the SET USER privilege, you can specify any syntactically legal account name. If the account does not actually exist, a warning is generated.
If the SQL SECURITY value is DEFINER but the definer account does not exist when the view is referenced, an error occurs.
Within a view definition, CURRENT_USER returns the view's DEFINER value by default. For views defined with the SQL SECURITY INVOKER characteristic, CURRENT_USER returns the account for the view's invoker. For information about user auditing within views, seeaccount-activity-auditing.html.
Within a stored routine that is defined with the SQL SECURITY DEFINER characteristic, CURRENT_USER returns the routine's DEFINER value. This also affects a view defined within such a program, if the view definition contains a DEFINER value of CURRENT_USER.
View privileges are checked like this:
At view definition time, the view creator must have the privileges needed to use the top-level objects accessed by the view. For example, if the view definition refers to table columns, the creator must have privileges for the columns, as described previously. If the definition refers to a stored function, only the privileges needed to invoke the function can be checked. The privileges required when the function runs can be checked only as it executes: For different invocations of the function, different execution paths within the function might be taken.
When a view is referenced, privileges for objects accessed by the view are checked against the privileges held by the view creator or invoker, depending on whether the SQL SECURITY characteristic is DEFINER or INVOKER, respectively.
If reference to a view causes execution of a stored function, privilege checking for statements executed within the function depend on whether the function is defined with a SQL SECURITY characteristic of DEFINER or INVOKER. If the security characteristic is DEFINER, the function runs with the privileges of its creator. If the characteristic is INVOKER, the function runs with the privileges determined by the view's SQL SECURITY characteristic.
Example: A view might depend on a stored function, and that function might invoke other stored routines. For example, the following view invokes a stored function f():
CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name);
Suppose that f() contains a statement such AS this:
IF name IS NULL THEN
CALL p1();
ELSE
CALL p2();
END IF;The privileges required for executing statements within f() need to be checked when f() executes. This might mean that privileges are needed for p1() or p2(), depending on the execution path within f(). Those privileges must be checked at runtime, and the user who must possess the privileges is determined by the SQL SECURITY values of the view v and the function f().
The DEFINER and SQL SECURITY clauses for views are extensions to standard SQL. In standard SQL, views are handled using the rules for SQL SECURITY INVOKER.
If you invoke a view that was created before MySQL 5.1.2, it is treated as though it was created with a SQL SECURITY DEFINER clause and with a DEFINER value that is the same as your account. However, because the actual definer is unknown, MySQL issues a warning. To make the warning go away, it is sufficient to re-create the view so that the view definition includes a DEFINER clause.
The optional ALGORITHM clause is an extension to standard SQL. It affects how MariaDB processes the view. ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED. The default algorithm is UNDEFINED if no ALGORITHM clause is present. See View Algorithms for more information.
Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view non-updatable. See Inserting and Updating with Views.
The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true.
In a WITH CHECK OPTION clause for an updatable view, the LOCAL and CASCADED keywords determine the scope of check testing when the view is defined in terms of another view. The LOCAL keyword restricts the CHECK OPTION only to the view being defined. CASCADED causes the checks for underlying views to be evaluated as well. When neither keyword is given, the default is CASCADED.
For more information about updatable views and the WITH CHECK OPTION clause, seeInserting and Updating with Views.
When the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the specified view already exists. Cannot be used together with the OR REPLACE clause.
MariaDB starting with 10.6.1
MariaDB 10.6.1 supports Atomic DDL and CREATE VIEW is atomic.
CREATE TABLE t (a INT, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,1), (2,2), (3,3);
CREATE VIEW v AS SELECT a, a*2 AS a2 FROM t;
SELECT * FROM v;
+------+------+
| a | a2 |
+------+------+
| 1 | 2 |
| 2 | 4 |
| 3 | 6 |
+------+------+OR REPLACE and IF NOT EXISTS:
CREATE VIEW v AS SELECT a, a*2 AS a2 FROM t;
ERROR 1050 (42S01): Table 'v' already exists
CREATE OR REPLACE VIEW v AS SELECT a, a*2 AS a2 FROM t;
Query OK, 0 rows affected (0.04 sec)
CREATE VIEW IF NOT EXISTS v AS SELECT a, a*2 AS a2 FROM t;
Query OK, 0 rows affected, 1 warning (0.01 sec)
SHOW WARNINGS;
+-------+------+--------------------------+
| Level | Code | Message |
+-------+------+--------------------------+
| Note | 1050 | Table 'v' already exists |
+-------+------+--------------------------+This page is licensed: GPLv2, originally from fill_help_tables.sql
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]DROP VIEW removes one or more views. You must have the DROP privilege for
each view. If any of the views named in the argument list do not exist, MariaDB
returns an error indicating by name which non-existing views it was unable to
drop, but it also drops all of the views in the list that do exist.
The IF EXISTS clause prevents an error from occurring for views that don't
exist. When this clause is given, a NOTE is generated for each non-existent
view. See SHOW WARNINGS.
RESTRICT and CASCADE, if given, are parsed and ignored.
It is possible to specify view names as db_name.view_name. This is useful to delete views from multiple databases with one statement. See Identifier Qualifiers for details.
The DROP privilege is required to use DROP TABLE on non-temporary tables. For temporary tables, no privilege is required, because such tables are only visible for the current session.
If a view references another view, it will be possible to drop the referenced view. However, the other view will reference a view which does not exist any more. Thus, querying it will produce an error similar to the following:
ERROR 1356 (HY000): View 'db_name.view_name' references invalid table(s) or
column(s) or function(s) or definer/invoker of view lack rights to use themThis problem is reported in the output of CHECK TABLE.
Note that it is not necessary to use DROP VIEW to replace an existing view, because CREATE VIEW has an OR REPLACE clause.
MariaDB starting with 10.6.1
MariaDB 10.6.1 supports Atomic DDL and DROP VIEW for a singular view is atomic. Dropping multiple views is crash-safe.
DROP VIEW v,v2;Given views v and v2, but no view v3
DROP VIEW v,v2,v3;
ERROR 1051 (42S02): Unknown table 'v3'DROP VIEW IF EXISTS v,v2,v3;
Query OK, 0 rows affected, 1 warning (0.01 sec)
SHOW WARNINGS;
+-------+------+-------------------------+
| Level | Code | Message |
+-------+------+-------------------------+
| Note | 1051 | Unknown table 'test.v3' |
+-------+------+-------------------------+This page is licensed: GPLv2, originally from fill_help_tables.sql
The Information Schema VIEWS table contains information about views. The SHOW VIEW privilege is required to view the table.
It has the following columns:
TABLE_CATALOG
Aways def.
TABLE_SCHEMA
Database name containing the view.
TABLE_NAME
View table name.
VIEW_DEFINITION
Definition of the view.
CHECK_OPTION
YES if the WITH CHECK_OPTION clause has been specified, NO otherwise.
IS_UPDATABLE
Whether the view is updatable or not.
DEFINER
Account specified in the DEFINER clause (or the default when created).
SECURITY_TYPE
SQL SECURITY characteristic, either DEFINER or INVOKER.
CHARACTER_SET_CLIENT
The client character set when the view was created, from the session value of the character_set_client system variable.
COLLATION_CONNECTION
The client collation when the view was created, from the session value of the collation_connection system variable.
ALGORITHM
The algorithm used in the view. See View Algorithms.
SELECT * FROM information_schema.VIEWS\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: v
VIEW_DEFINITION: SELECT `test`.`t`.`qty` AS `qty`,`test`.`t`.`price` AS `price`,(`test`.`t`.`qty` * `test`.`t`.`price`) AS `value` FROM `test`.`t`
CHECK_OPTION: NONE
IS_UPDATABLE: YES
DEFINER: root@localhost
SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
ALGORITHM: UNDEFINEDThis page is licensed: CC BY-SA / Gnu FDL
A view can be used for inserting or updating. However, there are certain limitations.
A view cannot be used for updating if it uses any of the following:
ALGORITHM=TEMPTABLE (see View Algorithms)
subquery in the SELECT list
subquery in the WHERE clause referring to a table in the FROM clause
if it has no underlying table because it refers only to literal values
the FROM clause contains a non-updatdable view.
multiple references to any base table column
an outer join
an inner join where more than one table in the view definition is being updated
if there's a LIMIT clause, the view does not contain all primary or not null unique key columns from the underlying table and the updatable_views_with_limit system variable is set to 0.
A view cannot be used for inserting if it fails any of the criteria for updating, and must also meet the following conditions:
the view contains all base table columns that don't have default values
no base table columns are present in view select list more than once
the view columns are all simple columns, and not derived in any way. The following are examples of derived columns
column_name + 25
LOWER(column_name)
(subquery)
9.5
column1 / column2
MariaDB stores an IS_UPDATABLE flag with each view, so it is always possible to see if MariaDB considers a view updatable (although not necessarily insertable) by querying the IS_UPDATABLE column in the INFORMATION_SCHEMA.VIEWS table.
The WITH CHECK OPTION clause is used to prevent updates or inserts to views unless the WHERE clause in the SELECT statement is true.
There are two keywords that can be applied. WITH LOCAL CHECK OPTION restricts the CHECK OPTION to only the view being defined, while WITH CASCADED CHECK OPTION checks all underlying views as well. CASCADED is treated as default if neither keyword is given.
If a row is rejected because of the CHECK OPTION, an error similar to the following is produced:
ERROR 1369 (HY000): CHECK OPTION failed 'db_name.view_name'A view with a WHERE which is always false (like WHERE 0) and WITH CHECK OPTION is similar to a BLACKHOLE table: no row is ever inserted and no row is ever returned. An insertable view with a WHERE which is always false but no CHECK OPTION is a view that accepts data but does not show them.
CREATE TABLE table1 (x INT);
CREATE VIEW view1 AS SELECT x, 99 AS y FROM table1;Checking whether the view is updateable:
SELECT TABLE_NAME,IS_UPDATABLE FROM INFORMATION_SCHEMA.VIEWS;
+------------+--------------+
| TABLE_NAME | IS_UPDATABLE |
+------------+--------------+
| view1 | YES |
+------------+--------------+This query works, as the view is updateable:
UPDATE view1 SET x = 5;This query fails, since column y is a literal.
UPDATE view1 SET y = 5;
ERROR 1348 (HY000): Column 'y' is not updatableHere are three views to demonstrate the WITH CHECK OPTION clause.
CREATE VIEW view_check1 AS SELECT * FROM table1 WHERE x < 100 WITH CHECK OPTION;
CREATE VIEW view_check2 AS SELECT * FROM view_check1 WHERE x > 10 WITH LOCAL CHECK OPTION;
CREATE VIEW view_check3 AS SELECT * FROM view_check1 WHERE x > 10 WITH CASCADED CHECK OPTION;This insert succeeds, as view_check2 only checks the insert against view_check2, and the WHERE clause evaluates to true (150 is >10).
INSERT INTO view_check2 VALUES (150);This insert fails, as view_check3 checks the insert against both view_check3 and the underlying views. The WHERE clause for view_check1 evaluates as false (150 is >10, but 150 is not <100), so the insert fails.
INSERT INTO view_check3 VALUES (150);
ERROR 1369 (HY000): CHECK OPTION failed 'test.view_check3'This page is licensed: CC BY-SA / Gnu FDL
The CREATE VIEW statement accepts an optional ALGORITHM clause, an extension to standard SQL for Views.
It can contain one of three values: MERGE, TEMPTABLE or UNDEFINED, and affects how MariaDB will process the view.
With MERGE, the view definition and the related portion of the statement referring to the view are merged. If TEMPTABLE is selected, the view results are stored in a temporary table.
MERGE is usually more efficient, and a view can only be updated with this algorithm. TEMPTABLE can be useful in certain situations, as locks on the underlying tables can be released before the statement is finished processing.
If it's UNDEFINED (or the ALGORITHM clause is not used), MariaDB will choose what it thinks is the best algorithm. An algorithm can also be UNDEFINED if its defined as MERGE, but the view requires a temporary table.
Views with definition ALGORITHM=MERGE or ALGORITHM=TEMPTABLE got accidentally swapped between MariaDB and MySQL. When upgrading, you have to re-create views created with either of these definitions (see MDEV-6916).
A view cannot be of type ALGORITHM=MERGE if it uses any of the following:
subquery in the SELECT list
if it has no underlying table because it refers only to literal values
Here's an example of how MariaDB handles a view with a MERGE algorithm. Take a view defined as follows:
CREATE ALGORITHM = MERGE VIEW view_name (view_field1, view_field2) AS
SELECT field1, field2 FROM table_name WHERE field3 > '2013-06-01';Now, if we run a query on this view, as follows:
SELECT * FROM view_name;to execute the view view_name becomes the underlying table, table_name, the * becomes the fields view_field1 and view_field2, corresponding to field1 and field2 and the WHERE clause, WHERE field3 > 100 is added, so the actual query executed is:
SELECT field1, field2 FROM table_name WHERE field3 > '2013-06-01'Given the same view as above, if we run the query:
SELECT * FROM view_name WHERE view_field < 8000;everything occurs as it does in the previous example, but view_field < 8000 takes the corresponding field name and becomes field1 < 8000, connected with AND to the field3 > '2013-06-01' part of the query.
So the resulting query is:
SELECT field1, field2 FROM table_name WHERE (field3 > '2013-06-01') AND (field1 < 8000);When connecting with AND, parentheses are added to make sure the correct precedence is used.
This page is licensed: CC BY-SA / Gnu FDL