Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement to create a view, which is a preset query stored in a database. In certain situations, a view can be useful for improved security. Views are available as of version 5.0.2 of MySQL.

The contents of a view are based on the SELECT statement given in the AS clause. Users can subsequently issue queries and updates to the view in place of a table; updates ultimately change the data in the tables that underlie the views.

The name of the view cannot be the same as a table in the database, because they share the same tablespace. A view can be based on other views, rather than directly based on a table. To label the column headings for the view’s results set, column names may be given in a comma-separated list in parentheses after the view name. This SQL statement is available as of version 5.0.1 of MySQL.

A few parameters may appear between the CREATE and VIEW keywords. By default, attempts to create a view with the name of an existing view will fail, but the OR REPLACE parameter will overwrite a view with the same name if it exists and will create a new view otherwise. Also by default, the view’s definer (used to determine access rights to the columns of the view) is the user who creates it, but another user can be specified with the DEFINER clause. This clause is available as of version 5.1.2 of MySQL. This version also introduced the related SQL SECURITY clause, which instructs MySQL to authorize access to the view based on the privileges of either the user account of the view’s creator (DEFINER, the default) or the user account of the user who is querying the view (INVOKER). This can help prevent some users from accessing restricted views.

The ALGORITHM parameter selects one of the two types of algorithmic methods to use for processing a view: MERGE or TEMPTABLE. TEMPTABLE prevents a view from being updatable. The default of UNDEFINED leaves the choice to MySQL.

The WITH CHECK OPTION clause restricts updates to rows in which the WHERE clause of the underlying SELECT statement returns true. For a view that is based on another view, if you include the LOCAL keyword, this restriction will be limited to the view in which it’s given and not the underlying view. Conversely, if you use the default choice of CASCADED, the WHERE clauses of underlying views will be considered as well.

If the mysqld server is started with the --updatable_views_with_limit option, updates that contain a LIMIT clause can update views only if the views contain all of the columns that are part of the primary keys of the underlying tables. If set to the default value of 1, only a warning is returned and updates are not restricted.

Here is an example of how you can use this statement:

CREATE DEFINER = 'russell'@'localhost'
SQL SECURITY INVOKER
VIEW student_directory(ID, Name, Telephone) 
AS SELECT student_id, 
CONCAT(name_first, SPACE(1), name_last), phone_home
FROM students;

This SQL statement creates a view that contains each student’s identification number, the student’s first and last name concatenated together with a space between, and the student’s home telephone number. To retrieve this data, enter the following SQL statement:

SELECT * FROM student_directory 
WHERE Name LIKE '%Tears';
+-----------+-------------------+-----------+
| ID        | Name              | Telephone |
+-----------+-------------------+-----------+
| 433342000 | Christina Tears   | 4883831   |  
+-----------+-------------------+-----------+

To save space in the output, the query includes a WHERE clause to retrieve a student with the last name of Tears. Notice that the column names are the ones named by the CREATE VIEW statement, not the underlying tables on which the view is based. This view will be available for all users who have SELECT privileges for the database in which it was created.

By default, a view is created in the default database at the time that the CREATE VIEW statement is entered. To create a view in a different database, simply add the database name and a dot as a separator in front of the view name in the CREATE VIEW statement.

To delete a view from a database, use the DROP VIEW statement. To see a list of existing views for the current database, run SHOW FULL TABLES WHERE Table_type='VIEW';.