Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.8Mb
PDF (A4) - 33.8Mb
PDF (RPM) - 31.8Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 145.9Kb
Man Pages (Zip) - 206.8Kb
Info (Gzip) - 3.1Mb
Info (Zip) - 3.1Mb


Pre-General Availability Draft: 2017-11-22

13.1.30 DROP VIEW Syntax

DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
    [RESTRICT | CASCADE]

DROP VIEW removes one or more views. You must have the DROP privilege for each view.

If any views named in the argument list do not exist, the statement fails with an error indicating by name which nonexisting views it was unable to drop, and no changes are made.

Note

In MySQL 5.7 and earlier, DROP VIEW returns an error if any views named in the argument list do not exist, but also drops all views in the list that do exist. Due to the change in behavior in MySQL 8.0, a partially completed DROP VIEW operation on a MySQL 5.7 master fails when replicated on a MySQL 8.0 slave. To avoid this failure scenario, use IF EXISTS syntax in DROP VIEW statements to prevent an error from occurring for views that do not exist. For more information, see Section 13.1.1, “Atomic Data Definition Statement Support”.

The IF EXISTS clause prevents an error from occurring for views that don't exist. When this clause is given, a NOTE is generated for each nonexistent view. See Section 13.7.6.40, “SHOW WARNINGS Syntax”.

RESTRICT and CASCADE, if given, are parsed and ignored.


User Comments
  Posted by Denis TRUFFAUT on June 3, 2012
A short explanation about multi drop view statements :

-- CREATE view1
CREATE OR REPLACE ALGORITHM=MERGE VIEW view1 AS
SELECT id FROM mytable WHERE id < 5
WITH CHECK OPTION;

-- DROP view2 (not existing) and view1 (existing)
DROP VIEW view2, view1;

-- Do some statements after drop
SELECT id FROM mytable LIMIT 0,5

---------------------------------

-- Now, what happens ?

-- An error is generated for view2
-- (ERROR 1146 (42S02) at line 2: Table 'mydatabase.view1' doesn't exist)

-- view1 is dropped

-- It means, that even if a drop fail in a multi drop view statement, other drops are executed, independently. That is a noticeable particularity.

-- Execution is stopped

-- Of course, if a multi drop view statement fails, other statements after it (create, drop, select, alter...) will not be executed, as expected.

Tested with 5.6.4-m7-log.
Sign Up Login You must be logged in to post a comment.