MySQL 快速上手

# 1.简介

视图（View）是一个存储在数据库中的查询语句，可以被重复使用。

视图本身不包含数据，数据库中只存储视图的定义语句。视图在许多情况下可以当作表来使用，因此也被称为虚拟表（Virtual Table）。

在这里插入图片描述

MySQL 中，存储例程包括存储过程和函数。存储程序包括存储例程、触发器和事件。存储对象包括存储程序和视图。

# 2.优缺点

视图是从一个或多个表中派生的虚拟表，它允许用户定义复杂的查询，并将其封装为一个单独的对象。

合理使用视图可以给我们带来许多好处：

  • 简化查询。将复杂的查询语句定义为视图，然后使用视图进行查询，可以隐藏具体的实现。
  • 统一接口。在视图的定义中增加业务逻辑，对外提供统一的接口；当底层表结构发生变化时，只需要修改视图接口，而不需要修改外部应用，可以简化代码的维护并减少错误。
  • 安全控制。通过视图为用户提供数据访问，而不是直接访问表；同时可以限制允许访问某些敏感信息，例如身份证号、工资等。
  • 数据抽象。视图可以将多个表的数据结合在一起，提供更具体、更有意义的数据视图。这对于业务报表和分析非常有用。

另一方面，视图使用不当也可能导致性能问题。

  • 性能开销。视图的定义中如果包含了复杂的查询，例如嵌套的子查询和多个表的连接查询，可能导致使用视图进行查询时性能不佳。因此，在使用视图之前最好进行相关的性能测试。
  • 更新限制：视图一般是只读的，某些情况下无法进行更新操作。
  • 复杂性：过多的视图可能导致数据库变得复杂难以维护。

MySQL 视图是一个强大的工具，可以简化查询、提高安全性和数据抽象能力。虽然视图可能带来一些性能开销，并且有一些限制，但在合适的场景下，使用视图可以显著改善数据库查询和数据管理的体验。

# 3.可更新视图

通常来说，视图主要用于查询数据。但是某些视图也可以用于修改数据，这种视图被称为可更新视图（Updatable View）。

可更新视图是指通过视图更新底层表，对视图的 INSERT、UPDATE、DELETE 等操作最终会转换为针对底层基础表的相应操作。

MySQL 可更新视图和基础表的数据行之间必须存在一一对应的关系，并且视图定义中不能出现以下内容：

  • 聚合函数或窗口函数，例如 AVG、SUM、COUNT 等。
  • DISTINCT、GROUP BY、HAVING 子句。
  • UNION 和 UNION ALL。
  • SELECT 列表中的子查询。非关联子查询不支持 INSERT，但是支持 UPDATE 和 DELETE；关联子查询不支持所有的 DML 语句。
  • 外连接查询。
  • 在 FROM 子句中引用其他不可更新视图。
  • WHERE 子句中的子查询引用 FROM 子句中的表。
  • 视图只引用了常量值（没有使用任何基础表）。
  • ALGORITHM = TEMPTABLE，使用临时表的视图。
  • 多次引用基础表中的某个字段（不支持 INSERT，但是可以 UPDATE 或者 DELETE）。

有时候多表连接视图也可以更新，前提是只能使用内连接查询。不过，只有其中一个表可以被更新，因此 SET 子句只能修改视图中同一个表中的字段。

# 4.创建视图

# 4.1 语法

MySQL 使用 CREATE VIEW 语句创建视图。

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

其中，OR REPLACE 表示如果视图已经存在，则替换视图的定义。

view_name 是视图名称。指定视图名称时可以显示指定视图属于哪个数据库，即 db_name.view_name。在数据库中，基表和视图共享相同的名称空间，因此基表和视图不能具有相同的名称。

column_list 是可选的逗号分隔的字段名，字段数必须与 SELECT 语句检索的列数相同。省略时使用查询语句返回的字段名。

select_statement 是视图的定义，也就是一个 SELECT 语句。

# ALGORITHM

ALGORITHM 子句是 MySQL 对标准 SQL 的扩展，用于指定在创建视图时要使用的查询优化算法。

ALGORITHM 有三个值 UNDEFINED | MERGE | TEMPTABLE。

（1）MERGE

使用 MERGE 时，将引用视图的语句和视图定义合并在一起，以便优化执行计划。

假设有一个视图 v_merge 具有以下定义：

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;

假设我们写出下面的查询语句：

SELECT * FROM v_merge;

MySQL对语句的处理如下：

  • v_merge 变成 t
  • * 变成 vc1, vc2 对应 c1, c2
  • 添加视图 WHERE 子句

执行的结果语句变为：

SELECT c1, c2 FROM t WHERE c3 > 100;

再比如我们写出下面的查询语句：

SELECT * FROM v_merge WHERE vc1 < 100;

那么引用视图的语句和视图定义合并在一起后的结果是：

SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);

（2）TEMPTABLE

使用 TEMPTABLE 时，视图的结果被检索到临时表中，然后使用该临时表来执行语句。这个算法可能会导致性能下降，因为它涉及到额外的临时表创建和数据处理。

显式指定 TEMPTABLE 的一个原因是，可以在创建临时表之后、使用临时表完成语句处理之前释放基础表上的锁。 这可能会导致比 MERGE 算法更快的锁定释放，以便使用该视图的其他客户端不会被阻塞太久。

（3）UNDEFINED

使用 UNDEFINED，MySQL 自动选择使用哪个算法。如果可能的话，它更倾向于 MERGE 而不是 TEMPTABLE，因为MERGE通常更有效，而且如果使用临时表，则视图无法更新。

如果不存在 ALGORITHM 子句，则默认算法由 optimizer_switch 系统变量的 derived_merge 标志的值确定。

# 权限

DEFINER 和 SQL SECURITY 子句确定在执行视图的语句时检查视图的访问权限时要使用哪个 MySQL 帐户。有效的 SQL SECURITY 特征值为 DEFINER（默认值）和 INVOKER。 这些表明所需的权限必须分别由定义或调用视图的用户拥有。

如果存在 DEFINER 子句，则用户值应为指定为 'user_name'@'host_name'、CURRENT_USER 或 CURRENT_USER() 的 MySQL 帐户，允许的用户值取决于您拥有的权限。缺省为当前执行创建操作的用户。

# WITH CHECK OPTION

WITH CHECK OPTION 是在创建可更新视图（Updatable View）时的一个子句，以防止插入或更新行（select_statement 中的WHERE 子句为 true 的行除外），从而保持视图和基础表的一致性。

假设您有一个可更新视图 employee_view，它从一个基础表 employees 中选择部分数据。您可以使用 WITH CHECK OPTION 子句来限制只有工资大于等于 3000 的员工数据才能被插入到视图中：

CREATE OR REPLACE VIEW employee_view AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary >= 3000
WITH CHECK OPTION;

在这个示例中，如果您尝试插入工资低于 3000 的数据到 employee_view 中，MySQL 将阻止此操作并抛出错误。

需要注意的是，WITH CHECK OPTION 子句仅在创建可更新视图时使用，用于保持视图和基础表之间的一致性。

在可更新视图的 WITH CHECK OPTION 子句中，当根据另一个视图定义该视图时，LOCAL 和 CASCADED 关键字确定检查测试的范围。 LOCAL 关键字将 CHECK OPTION 限制为仅针对正在定义的视图。 CASCADED 也会对依赖的视图进行检查。缺省为 CASCADED。

MySQL 系统视图 INFORMATION_SCHEMA.VIEWS 中记录了视图是否可以更新以及 CHECK OPTION 选项。

SELECT table_name, is_updatable, check_option
FROM INFORMATION_SCHEMA.VIEWS
WHERE table_schema = 'db_name';

# 4.2 示例

可以从多种 SELECT 语句创建视图。 它可以引用基表或其他视图。 它可以使用 JOIN、UNION 和子查询。

SELECT 甚至不需要引用任何表。

CREATE VIEW v_today (today) AS SELECT CURRENT_DATE;

以下示例定义了一个视图，该视图从另一个表中选择两列，以及根据这些列计算的表达式。

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50), (5, 60);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
|    5 |    60 |   300 |
+------+-------+-------+
mysql> SELECT * FROM v WHERE qty = 5;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    5 |    60 |   300 |
+------+-------+-------+

# 5.查看视图

SHOW FULL TABLES 语句除了可以列出当前数据库中所有的表之外，也可以用于查看视图。

SHOW FULL TABLES 
WHERE table_type = 'VIEW';

+------------------------------+------------+
| Tables_in_prod_center_dev_db | Table_type |
+------------------------------+------------+
| v                            | VIEW       |
+------------------------------+------------+
1 row in set (0.06 sec)

使用 SHOW CREATE VIEW 语句查看视图的定义。

SHOW CREATE VIEW v;

*************************** 1. row ***************************
                View: v
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`test`@`%` SQL SECURITY DEFINER VIEW `v` AS select `t`.`qty` AS `qty`,`t`.`price` AS `price`,(`t`.`qty` * `t`.`price`) AS `value` from `t`
character_set_client: utf8
collation_connection: utf8_general_ci

视图的完整信息可以从 INFORMATION_SCHEMA 数据库的系统视图 VIEWS 中查看。

SELECT view_definition
FROM INFORMATION_SCHEMA.VIEWS
WHERE table_name = 'v';

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| view_definition                                                                                                                                                                                         |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| select `prod_center_dev_db`.`t`.`qty` AS `qty`,`prod_center_dev_db`.`t`.`price` AS `price`,(`prod_center_dev_db`.`t`.`qty` * `prod_center_dev_db`.`t`.`price`) AS `value` from `prod_center_dev_db`.`t` |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

# 6.修改视图

如果需要修改视图的定义，可以使用 CREATE OR REPLACE VIEW 语句，或者使用 ALTER VIEW 语句。

ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

ALTER VIEW 语句和 CREATE VIEW 语句的参数完全一致。待修改的视图必须存在，否则报错。

比如修改前文创建的视图 t，修改 value 列名为 total_price。

ALTER VIEW v
AS
SELECT qty, price, qty*price AS total_price FROM t;

mysql> SELECT * FROM v;
+------+-------+-------------+
| qty  | price | total_price |
+------+-------+-------------+
|    3 |    50 |         150 |
|    5 |    60 |         300 |
+------+-------+-------------+

另外，RENAME TABLE 语句也可以用于视图重命名。

RENAME TABLE
    tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2] ...

比如将视图 v 重命名为 price_view。

RENAME TABLE v TO price_view;

# 7.删除视图

MySQL 使用 DROP VIEW 命令删除视图。

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

如果删除不存在的视图将返回错误，指定 IF EXISTS 不会产生错误。

RESTRICT（缺省） 或 CASCADE 关键字来指定在删除视图时如何处理相关的对象和依赖关系。

使用 RESTRICT，则 MySQL 将检查视图是否存在与其相关联的其他依赖关系，如触发器、存储过程或其他视图。如果存在任何依赖关系，MySQL 将阻止删除视图，并抛出一个错误。这是一种保护机制，用于防止意外删除视图并影响依赖关系。

使用 CASCADE，则 MySQL 将删除视图的同时，会递归删除所有与之相关的依赖关系。这意味着视图、触发器、存储过程等会被一并删除。使用 CASCADE 可以方便地删除视图及其所有相关对象，但要注意潜在的影响。

# 参考文献

MySQL 8.0 Reference Manual :: 25.5 Using Views (opens new window)

25.5.3 Updatable and Insertable Views (opens new window)

13.1.23 CREATE VIEW Statement (opens new window)

13.7.7.39 SHOW TABLES Statement (opens new window)

13.1.11 ALTER VIEW Statement (opens new window)

13.1.36 RENAME TABLE Statement (opens new window)

13.1.35 DROP VIEW Statement (opens new window)

MySQL 入门教程》第 29 篇 视图 (opens new window)

← 游标 →