Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement to change a view. Views are available as of version 5.0.1 of MySQL.

The statement is used primarily to change the SELECT statement that determines the view, which you can do simply by placing the new SELECT statement for the view after the AS keyword.

Change the column names provided by the view queries by providing the new column names in a comma-separated list within the parentheses following the view’s name. Don’t include either the old SELECT statement or the old column names in the statement.

The ALGORITHM parameter changes algorithmic methods to use for processing a view: the choices are MERGE or TEMPTABLE. TEMPTABLE prevents a view from being updatable.

The DEFINER clause can change the user account considered to be the view’s creator. This clause is available as of version 5.1.2 of MySQL. The same version introduced the related SQL SECURITY clause. It instructs MySQL to authorize access to the view based on the privileges of either the user account of the view’s creator (DEFINER) or the user account of the user who is querying the view (INVOKER). This can help prevent some users from accessing restricted views.

The WITH CHECK OPTION clause can change the restrictions on the updating of a view to only 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. If you specify CASCADED instead, underlying views will be considered as well.

Here is an example of this statement’s use:

ALTER VIEW student_directory(ID, Name, Cell_Telephone, Home_Telephone) 
AS SELECT student_id, 
CONCAT(name_first, SPACE(1), name_last), 
phone_dorm, phone_home
FROM students;

If you look at the example for CREATE VIEW later in this chapter, you’ll see that we’re adding an extra column to the view created in that example. The other settings remain unchanged.

You cannot change the name of an existing view. Instead, use the DROP VIEW statement and then create a new view with the CREATE VIEW statement.