Create an unmaterialized view.

Syntax

CREATE
    [DEFINER = { user | CURRENT_USER }]
    VIEW view_name
    AS select_statement

Remarks

• Views in MemSQL are not writable. Attempts to INSERT, UPDATE, or DELETE data in a view will result in an error. Instead, use ALTER VIEW or manipulate data directly in the underlying tables from which the view is constructed.
• MemSQL will always attempt to merge queries that refer to a view with the original view definition such that portions of the view definition replace semantically equivalent portions of the query. This default behavior is similar to MySQL’s ALGORITHM = MERGE option as part of CREATE VIEW. When a merge is not possible, MemSQL creates an internal temporary table.
• MemSQL does not support MySQL’s CREATE OR REPLACE VIEW syntax. To modify an existing view, use ALTER VIEW.
• By default, MemSQL restricts the ability to CREATE, ALTER, or SELECT from the view to the user who created the view and to SUPER users. In MySQL terms, this is the equivalent of including DEFINER = CURRENT_USER and SQL SECURITY DEFINER in the CREATE VIEW statement.
• Views cannot reference views or tables from other databases.
• Columns used in a view definition cannot be dropped. Running DROP TABLE on a table referenced by a view will give an error. Running ALTER TABLE DROP COLUMN col will give an error when col is referenced by a view. Columns not referenced by any view can be dropped.
• CREATE VIEW view_name AS SELECT * FROM table_name ... will replace * with the column names at the time of CREATE VIEW execution rather than during execution of SELECT ... FROM view_name. It is important to consider this distinction if table_name is altered between the execution of CREATE VIEW and SELECT ... FROM view_name.
• Any function (including CURRENT_USER() and NOW()) used in a view are evaluated during execution of SELECT ... FROM view_name, not when the view is created.
• The DEFINER clause tells MemSQL which user should be used for security checks when a view is referenced by a query. The default value is CURRENT_USER.

Examples

memsql> CREATE VIEW view_name
 -> AS SELECT * FROM table_name WHERE user_id = "real_person";

memsql> create view active_items_view as
 -> select * from items where status = 'active';

memsql> create view discounted_items_view as
 -> select * from active_items_view where discount = 1;

Related Topics

• ALTER VIEW
• DROP VIEW

Was this Article Helpful?

Thanks For Your Feedback!