第 29 篇 视图
《MySQL 入门教程》第 29 篇 视图
本篇我们介绍 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 developer
AS
SELECT emp_id, emp_name, sex, manager, hire_date, job_id, email
FROM employee
WHERE 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 d
JOIN 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, num
FROM developer_count
ORDER 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.views
WHERE 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`.`|
📝关于查看 MySQL 视图的详细方法可以参考这篇文章。
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 developer
AS
SELECT emp_id, emp_name, sex, manager, hire_date, job_id, email
FROM employee
WHERE 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 developer
AS
SELECT emp_id, emp_name, sex, manager, hire_date, job_id, email
FROM employee
WHERE dept_id = 4;
使用以上语句创建视图 developer 之后,当我们查询视图时:
SELECT *
FROM developer
WHERE sex = '女';
MySQL 执行视图合并之后,实际运行的查询语句如下:
SELECT *
FROM employee
WHERE dept_id = 4
AND sex = '女';
对于 TEMPTABLE 算法,MySQL 首先会执行视图定义中的 SELECT 语句并创建一个临时表,然后再基于临时表执行其他的查询返回结果。例如:
CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW developer
AS
SELECT emp_id, emp_name, sex, manager, hire_date, job_id, email
FROM employee
WHERE dept_id = 4;
使用以上语句创建视图 developer 之后,当我们查询视图时:
EXPLAIN ANALYZE
SELECT *
FROM developer
WHERE 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_updatable
AS
SELECT emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, email
FROM employee
WHERE dept_id = 4;
以下语句通过视图 developer_updatable 修改员工表中的 email:
UPDATE developer_updatable
SET email = 'zhaoshi@shuguo.net'
WHERE emp_name = '赵氏';
SELECT emp_name, email
FROM employee
WHERE emp_name = '赵氏';
emp_name|email |
--------|------------------|
赵氏 |zhaoshi@shuguo.net|
从查询的结果可以看出,针对视图 developer_updatable 的更新最终修改了 employee 中的数据。
不在视图定义中的字段不能通过视图进行修改。以下语句尝试通过视图修改 employee 中的 bonus 字段:
UPDATE developer_updatable
SET bonus = 2000
WHERE 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, sex
FROM employee
WHERE emp_name = '孙七七';
emp_id|emp_name|sex|
------|--------|---|
30|孙七七 |女 |
SELECT emp_id, emp_name, sex
FROM developer_updatable
WHERE emp_name = '孙七七';
emp_id|emp_name|sex|
------|--------|---|
为了防止这种情况的发生,可以在创建视图时指定WITH CHECK OPTION
选项。该选项用于限制对视图的插入和更新操作,不会产生对视图不可见的数据。我们重建视图 developer_updatable:
CREATE OR REPLACE VIEW developer_updatable
AS
SELECT emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, email
FROM employee
WHERE 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_option
FROM information_schema.views
WHERE 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;