DROP-MATERIALIZED-VIEW

Name

DROP MATERIALIZED VIEW

Description

This statement is used to drop a materialized view.

Syntax:

DROP MATERIALIZED VIEW [IF EXISTS] mv_name ON table_name;

1. IF EXISTS: If this keyword is stated, the system will not throw an error if the materialized view does not exist. Otherwise, the system will report an error if the materialized view does not exist.

2. mv_name: The name of the materialized view to be deleted. Required.

3. table_name: The name of the table to which the materialized view to be deleted belongs. Required.

Example

The table structure is as follows

mysql> desc all_type_table all;
+----------------+-------+----------+------+------ -+---------+-------+
| IndexName | Field | Type | Null | Key | Default | Extra |
+----------------+-------+----------+------+------ -+---------+-------+
| all_type_table | k1 | TINYINT | Yes | true | N/A | |
| | k2 | SMALLINT | Yes | false | N/A | NONE |
| | k3 | INT | Yes | false | N/A | NONE |
| | k4 | BIGINT | Yes | false | N/A | NONE |
| | k5 | LARGEINT | Yes | false | N/A | NONE |
| | k6 | FLOAT | Yes | false | N/A | NONE |
| | k7 | DOUBLE | Yes | false | N/A | NONE |
| | | | | | | | |
| k1_sumk2 | k1 | TINYINT | Yes | true | N/A | |
| | k2 | SMALLINT | Yes | false | N/A | SUM |
+----------------+-------+----------+------+------ -+---------+-------+

1. Drop the materialized view named k1_sumk2 of the table all_type_table

   drop materialized view k1_sumk2 on all_type_table;

   The table structure after the materialized view is deleted

   +----------------+-------+----------+------+------ -+---------+-------+
   | IndexName | Field | Type | Null | Key | Default | Extra |
   +----------------+-------+----------+------+------ -+---------+-------+
   | all_type_table | k1 | TINYINT | Yes | true | N/A | |
   | | k2 | SMALLINT | Yes | false | N/A | NONE |
   | | k3 | INT | Yes | false | N/A | NONE |
   | | k4 | BIGINT | Yes | false | N/A | NONE |
   | | k5 | LARGEINT | Yes | false | N/A | NONE |
   | | k6 | FLOAT | Yes | false | N/A | NONE |
   | | k7 | DOUBLE | Yes | false | N/A | NONE |
   +----------------+-------+----------+------+------ -+---------+-------+

2. Drop a non-existent materialized view in the table all_type_table

   drop materialized view k1_k2 on all_type_table;
   ERROR 1064 (HY000): errCode = 2, detailMessage = Materialized view [k1_k2] does not exist in table [all_type_table]

   The delete request reports an error directly

3. Delete the materialized view k1_k2 in the table all_type_table, if it does not exist, no error will be reported.

   drop materialized view if exists k1_k2 on all_type_table;
   Query OK, 0 rows affected (0.00 sec)

   If it exists, delete it, if it does not exist, no error is reported.

Keywords

DROP, MATERIALIZED, VIEW

Best Practice