本篇我们介绍 MySQL 中另一个重要的数据库对象：视图（View）。

29.1 基本概念

简单来说，视图就是一个存储在数据库中的查询语句，可以被重复使用。视图本身不包含数据，数据库中只存储视图的定义语句。视图在许多情况下可以当作表来使用，因此也被称为虚拟表（Virtual Table）。以下是视图的示意图：

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

• 替代复杂查询，减少复杂性。将复杂的查询语句定义为视图，然后使用视图进行查询，可以隐藏具体的实现；

• 提供一致性接口，实现业务规则。在视图的定义中增加业务逻辑，对外提供统一的接口；当底层表结构发生变化时，只需要修改视图接口，而不需要修改外部应用，可以简化代码的维护并减少错误；

• 控制对于表的访问，提高安全性。通过视图为用户提供数据访问，而不是直接访问表；同时可以限制允许访问某些敏感信息，例如身份证号、工资等。

另一方面，视图使用不当也可能导致性能问题。视图的定义中如果包含了复杂的查询，例如嵌套的子查询和多个表的连接查询，可能导致使用视图进行查询时性能不佳。因此，在使用视图之前最好进行相关的性能测试。

接下来我们介绍如何创建、使用、修改和删除视图。

29.2 创建视图

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

CREATE [OR REPLACE] VIEW view_name [(column1, column2, ...)]
AS select_statement;

其中，OR REPLACE表示如果该视图已经存在，替换视图的定义；view_name 是视图的名称；column1、column2 等是可选的字段名，省略时使用查询语句返回的字段名；select_statement 是视图的定义，也就是一个 SELECT 语句。

以下语句创建了一个名为 developers 的视图。其中只包含开发部门的员工，同时隐藏了月薪等敏感信息：

CREATE OR REPLACE VIEW developerASSELECT emp_id, emp_name, sex, manager, hire_date, job_id, emailFROM employeeWHERE dept_id = 4;

视图定义中的 SELECT 语句与普通的查询一样，可以包含任意复杂的选项，例如子查询、集合操作、分组聚合等。另外，视图既可以基于一个或多个表定义，也可以基于其他视图进行定义。

例如，以下语句基于视图 developer 与 job 表进行连接并创建一个新的视图：

CREATE OR REPLACE VIEW developer_count(jobtitle, sex, num)AS SELECT j.job_title, d.sex, COUNT(*)FROM developer dJOIN job j ON (j.job_id = d.job_id)GROUP BY j.job_title, d.sex;

视图 developer_count 包含了开发部门中按照职位和性别统计的人数。

⚠️虽然 MySQL 支持在视图定义中使用 ORDER BY 子句；但是 SQL 标准并不支持这种写法，因为视图并不存储数据。建议不要在视图的定义中使用 ORDER BY，因为这种排序并不能保证最终结果的顺序；而且可能由于不必要的排序降低查询的性能。

创建视图之后，可以像普通表一样将视图作为查询的数据源。以下语句使用视图 developer 进行查询：

SELECT jobtitle, sex, numFROM developer_countORDER BY num DESC;jobtitle |sex |num|---------|----|---|程序员    |男  |  7|开发经理  |男  |  1|程序员    |女  |  1|

29.3 查看视图

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

SHOW FULL TABLES WHERE table_type = 'VIEW';Tables_in_hrdb      |Table_type|--------------------|----------|developer           |VIEW      |developer_count     |VIEW      |emp_details_view    |VIEW      |

MySQL 使用SHOW CREATE VIEW语句查看视图的定义，例如：

SHOW CREATE VIEW developer;View     |Create View                                                                                                                                                                                                                                                    |character_set_client|collation_connection|---------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------|--------------------|developer|CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `developer` AS select `employee`.`emp_id` AS `emp_id`,`employee`.`emp_name` AS `emp_name`,`employee`.`sex` AS `sex`,`employee`.`manager` AS `manager`,`employee`.`hire_date` AS `hire_d|utf8mb4             |utf8mb4_0900_ai_ci  |

视图的完整信息存储在 INFORMATION_SCHEMA 数据库的 VIEWS 表中。例如：

SELECT view_definition 
FROM information_schema.viewsWHERE table_schema = 'hrdb'AND table_name = 'developer';VIEW_DEFINITION                                                                                                                                                                                                                                                |---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|select `hrdb`.`employee`.`emp_id` AS `emp_id`,`hrdb`.`employee`.`emp_name` AS `emp_name`,`hrdb`.`employee`.`sex` AS `sex`,`hrdb`.`employee`.`manager` AS `manager`,`hrdb`.`employee`.`hire_date` AS `hire_date`,`hrdb`.`employee`.`job_id` AS `job_id`,`hrdb`.`|

29.4 修改视图

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

ALTER VIEW view_name [(column1, column2, ...)]
AS select_statement;

ALTER VIEW 语句和 CREATE VIEW 语句的参数完全一致。以下语句修改了视图 developer 的定义，删除了 hire_date 字段：

ALTER VIEW developerASSELECT emp_id, emp_name, sex, manager, hire_date, job_id, emailFROM employeeWHERE dept_id = 4;

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

RENAME TABLE developer_count TO developer_job_count;

29.5 视图处理算法

CREATE VIEW 和 ALTER VIEW 语句支持一个可选项：ALGORITHM。该选项用于定义 MySQL 处理视图的算法，可能的取值为 MERGE、TEMPTABLE 或者 UNDEFINE（默认值）。

以 CREATE VIEW 语句为例，包含 ALGORITHM 子句的语法如下：

CREATE [OR REPLACE] ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}  VIEW view_name [(column1, column2, ...)]
AS select_statement;

对于 MERGE 算法，MySQL 首先会将视图定义中的 SELECT 和外部 SELECT 语句合并成一个查询，然后再执行查询返回结果。例如：

CREATE OR REPLACE ALGORITHM = MERGE VIEW developerASSELECT emp_id, emp_name, sex, manager, hire_date, job_id, emailFROM employeeWHERE dept_id = 4;

使用以上语句创建视图 developer 之后，当我们查询视图时：

SELECT *FROM developerWHERE sex = '女';

MySQL 执行视图合并之后，实际运行的查询语句如下：

SELECT *FROM employeeWHERE dept_id = 4AND sex = '女';

对于 TEMPTABLE 算法，MySQL 首先会执行视图定义中的 SELECT 语句并创建一个临时表，然后再基于临时表执行其他的查询返回结果。例如：

CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW developerASSELECT emp_id, emp_name, sex, manager, hire_date, job_id, emailFROM employeeWHERE dept_id = 4;

使用以上语句创建视图 developer 之后，当我们查询视图时：

EXPLAIN ANALYZESELECT *FROM developerWHERE sex = '女';EXPLAIN                                                                                                                                                                                                                                                        |---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-> Index lookup on developer using <auto_key0> (sex='女')  (actual time=0.006..0.008 rows=1 loops=1)
-> Materialize  (actual time=0.407..0.409 rows=1 loops=1)
-> Index lookup on employee using idx_emp_dept (dept_id=4)  (cost=1.62 rows=9) (actual time=0.194..0.215 rows=9 loops=1)|

EXPLAIN ANALYZE 语句用于查看 MySQL 执行计划，也就是服务器执行 SQL 语句的具体步骤。其中的 Materialize 表示创建临时表的物化过程。

如果没有指定算法选项，默认使用 UNDEFINED；或者指定了 MERGE 算法，但是由于某些原因无法使用视图合并时也会使用 UNDEFINED。此时，MySQL 可以自己选择具体的算法；通常来说，MySQL 会优先选择 MERGE 算法，因为它的效率更高。

29.6 可更新视图

通常来说，视图主要用于查询数据；但是某些视图也可以用于修改数据，这种视图被称为可更新视图（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 子句只能修改视图中同一个表中的字段。

我们创建一个简单的视图 developer_updatable，包含了 employee 中除 bonus 之外的所有字段：

CREATE OR REPLACE VIEW developer_updatableASSELECT emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, emailFROM employeeWHERE dept_id = 4;

以下语句通过视图 developer_updatable 修改员工表中的 email：

UPDATE developer_updatableSET email = 'zhaoshi@shuguo.net'WHERE emp_name = '赵氏';SELECT emp_name, emailFROM employeeWHERE emp_name = '赵氏';emp_name|email             |--------|------------------|赵氏     |zhaoshi@shuguo.net|

从查询的结果可以看出，针对视图 developer_updatable 的更新最终修改了 employee 中的数据。

不在视图定义中的字段不能通过视图进行修改。以下语句尝试通过视图修改 employee 中的 bonus 字段：

UPDATE developer_updatableSET bonus = 2000WHERE emp_name = '赵氏';ERROR 1054 (42S22): Unknown column 'bonus' in 'field list'

以上语句返回了一个错误：bonus 字段未知。因为视图 developer_updatable 中没有包含员工的奖金字段，这样可以防止敏感数据的误修改。

WITH CHECK OPTION 选项

虽然无法通过视图修改不可见的列，但是有可能修改视图可见范围之外的数据行。以下语句通过视图为员工表增加一个员工：

INSERT INTO developer_updatable(emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, email)VALUES (30, '孙七七', '女', 5, 18, '2020-09-10', 10, 6000, 'sunqiqi@shuguo.com');

该员工属于销售部（dept_id = 5），不在视图 developer_updatable 的可见范围之内；但是以上语句仍然能够执行成功，并且能够在员工表中找到该条记录：

SELECT emp_id, emp_name, sexFROM employeeWHERE emp_name = '孙七七';emp_id|emp_name|sex|------|--------|---|
30|孙七七   |女 |SELECT emp_id, emp_name, sexFROM developer_updatableWHERE emp_name = '孙七七';emp_id|emp_name|sex|------|--------|---|

为了防止这种情况的发生，可以在创建视图时指定WITH CHECK OPTION选项。该选项用于限制对视图的插入和更新操作，不会产生对视图不可见的数据。我们重建视图 developer_updatable：

CREATE OR REPLACE VIEW developer_updatableASSELECT emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, emailFROM employeeWHERE dept_id = 4 WITH CHECK OPTION;

然后再次执行上面的插入语句：

INSERT INTO developer_updatable(emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, email)VALUES (30, '孙七七', '女', 5, 18, '2020-09-10', 10, 6000, 'sunqiqi@shuguo.com');SQL Error [1369] [HY000]: CHECK OPTION failed 'hrdb.developer_updatable'

MySQL 返回了视图数据检查失败的错误信息，而不是主键冲突。

进一步来说，WITH CHECK OPTION 选项又可以分为两种：CASCADED 或者 LOCAL。对于 WITH CASCADED CHECK OPTION，MySQL 需要检查当前视图的限制以及该它依赖的其他视图的级联限制，这是默认值；对于 WITH LOCAL CHECK OPTION，MySQL 只检查这些视图中定义了 WITH LOCAL CHECK OPTION 或者 WITH CASCADED CHECK OPTION 的限制。

MySQL 系统表 information_schema.views 中记录了视图是否可以更新以及 CHECK OPTION 选项：

SELECT table_name, is_updatable, check_optionFROM information_schema.viewsWHERE table_schema = 'hrdb';TABLE_NAME         |IS_UPDATABLE|CHECK_OPTION|-------------------|------------|------------|developer          |NO          |NONE        |developer_count    |NO          |NONE        |developer_updatable|YES         |CASCADED    |emp_details_view   |YES         |NONE        |

29.7 删除视图

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

DROP VIEW [IF EXISTS] view_name;

指定 IF EXISTS 选项后，删除一个不存在的视图时也不会产生错误。例如，以下语句可以删除视图 developer：

DROP VIEW developer;