The select_statement is a
SELECT statement that provides the
definition of the view. (Selecting from the view selects, in
effect, using the SELECT
statement.) The select_statement can
select from base tables or other views.

The view definition is “frozen” at creation time and
is not affected by subsequent changes to the definitions of the
underlying tables. 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, and columns dropped
from the table will result in an error when selecting from the
view.

The ALGORITHM clause affects how MySQL
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. CREATE VIEW might also
require the SUPER privilege,
depending on the DEFINER value, as described
later in this section.

When a view is referenced, privilege checking occurs as described
later in this section.

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, use db_name.view_name syntax
to qualify the view name with the database name:

CREATE VIEW test.v AS SELECT * FROM t;

Unqualified table or view names in the
SELECT statement are also
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 appropriate database name.

Within a database, base tables and views share the same namespace,
so a base table and a view cannot have the same name.

Columns retrieved by the SELECT
statement can be simple references to table columns, or
expressions that use functions, constant values, operators, and so
forth.

A view must have unique column names with no duplicates, just like
a base table. 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, specify the optional
column_list clause 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.

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:

CREATE VIEW v_today (today) AS SELECT CURRENT_DATE;

The following example defines a view that selects two columns from
another table as well as an expression calculated from those
columns:

Any table or view referred to in the definition must exist.
If, after the view has been created, a table or view that the
definition refers to is dropped, 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.

You cannot associate a trigger with a view.

Aliases for column names in the
SELECT statement are checked
against the maximum column length of 64 characters (not the
maximum alias length of 256 characters).

ORDER BY is permitted in a view definition, but
it is ignored if you select from a view using a statement that has
its own ORDER BY or filtering or grouping. When
ORDER BY is combined with
LIMIT or OFFSET in a view
definition, the ordering is always enforced before the query
result is used by the outer query, but it does not guarantee that
the same ordering is used in the end result. As a workaround, add
an ORDER BY clause to the outer query.

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,
LOCK IN SHARE MODE, and
PROCEDURE.

The results obtained from a view may be affected if you change the
query processing environment by changing system variables:

The DEFINER and SQL SECURITY
clauses determine which MySQL account to use when checking access
privileges for the view when a statement is executed that
references the view. The valid SQL SECURITY
characteristic values are DEFINER (the default)
and INVOKER. These indicate that the required
privileges must be held by the user who defined or invoked the
view, respectively.

If a user value is given for the
DEFINER clause, it should be a MySQL account
specified as
'user_name'@'host_name',
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 the DEFINER clause is present, these rules
determine the valid DEFINER user values:

If you do not have the SUPER
privilege, the only valid 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, you can specify any syntactically valid account
name. If the account does not exist, a warning is generated.

Although it is possible to create a view with a nonexistent
DEFINER account, an error occurs when the
view is referenced if the SQL SECURITY
value is DEFINER but the definer account
does not exist.

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 routine, if the view definition contains a
DEFINER value of
CURRENT_USER.

MySQL checks view privileges 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 some privilege for each column
in the select list of the definition, and the
SELECT privilege for each
column used elsewhere in the definition. If the definition
refers to a stored function, only the privileges needed to
invoke the function can be checked. The privileges required at
function invocation time can be checked only as it executes:
For different invocations, different execution paths within
the function might be taken.

The user who references a view must have appropriate
privileges to access it (SELECT
to select from it, INSERT to
insert into it, and so forth.)

When a view has been referenced, privileges for objects
accessed by the view are checked against the privileges held
by the view DEFINER account 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 SQL SECURITY
characteristic is DEFINER or
INVOKER. If the security characteristic is
DEFINER, the function runs with the
privileges of the DEFINER account. 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
DEFINER. The standard says that the definer of the view,
which is the same as the owner of the view's schema, gets
applicable privileges on the view (for example,
SELECT) and may grant them. MySQL
has no concept of a schema “owner”, so MySQL adds a
clause to identify the definer. The DEFINER
clause is an extension where the intent is to have what the
standard has; that is, a permanent record of who defined the view.
This is why the default DEFINER value is the
account of the view creator.

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 nonupdatable.

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.

(example above is an overly simplified version of what I did for demonstration puposes).

Posted by
Rafael Palacios
on
November 14, 2007

Simulating Materialized ViewMaterialized 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.

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.

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":

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.

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