
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.

WITH CHECK OPTION
-----------------

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,
see Inserting and Updating with Views.

IF NOT EXISTS
-------------

MariaDB starting with 10.1.3
----------------------------
The IF NOT EXISTS clause was added in MariaDB 10.1.3

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.

Atomic DDL
----------

MariaDB starting with 10.6.1
----------------------------
MariaDB 10.6.1 supports Atomic DDL and CREATE VIEW is atomic.

Examples
--------

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 |
+-------+------+--------------------------+

URL: https://mariadb.com/kb/en/create-view/