时间戳

首页 > 技术文章 > MySQL 视图（View）

MySQL 视图（View）

codesee 2021-03-26 12:10 原文

MySQL View 视图： 存储在数据库里的查询语句。

  • 简化逻辑并复用；
  • 增加了一层安全控制：view只暴露表里部分字段，view可以用专门的privilege控制；
  • 向后兼容：某一个db table要被淘汰调或替换掉，可以创建和表名一样的view，然后应用代码就可以不用改动。

显示View列表

show full tables where table_type='VIEW';
或
show full tables from mysql_practice where table_type='VIEW';
或
show full tables in mysql_practice where table_type='VIEW';
或
SELECT table_name, table_comment 
FROM information_schema.tables 
where table_type='VIEW' and table_schema='mysql_practice';
  • 也可以使用 like

创建 View

语法

CREATE [OR REPLACE] VIEW [db_name.]view_name [(column_list)]
AS
  select-statement;

示例：
查询订单信息，包含book, customer的具体信息。

create or replace view view_customer_order as
select
co.no as order_no, -- 订单编号
co.order_date, -- 订单日期
co.total_price, -- 订单的总价
co.quantity, -- 订单里数据数量
cu.no customer_no, -- 客户编号
cu.first_name, -- 客户 名
cu.last_name, -- 客户 姓
bo.no as book_no, -- 图书 编号
bo.name as book_name, -- 书名
bo.author, -- 作者
bo.publisher, -- 出版社
bo.publish_date, -- 出版日期
bo.unit_price, -- 书 单价
bc.code as category_code, -- 书 分类码
bc.name as category_name -- 书 分类名
from customer_order co
join customer cu
on co.customer_id = cu.id
join book bo
on bo.id = co.book_id
join book_category bc
on bc.id = bo.category_id;

可更新View

上面创建的View，其实也是一个可更新View，意思是可以通过View来Update或Delete数据，如：

update view_customer_order 
set author = '杨开振'
where order_no = '8626494c80c8111102';

update view_customer_order 
set total_price = 118, -- order 表的字段
quantity = 3 -- order 表的字段
where order_no = '8626494c80c8111102';

不可以更新多个不同表的字段，如：

update view_customer_order 
set total_price = '118' , -- order 表的字段
	 author = '杨开振'  -- book表的字段
where order_no = '8626494c80c8111102';

会报错：

ERROR 1393 (HY000): Can not modify more than one base table through a join view 'mysql_practice.view_customer_order'

创建可更新View

创建的语法和正常view一样。如果包含以下情况，就会被认为是不可更新View：

  • Aggregate functions such as MIN, MAX, SUM, AVG, and COUNT.
  • DISTINCT
  • GROUP BY clause.
  • HAVING clause.
  • UNION or UNION ALL clause.
  • Left join or outer join.
  • Subquery in the SELECT clause or in the WHERE clause that refers to the table appeared in the FROM clause.
  • Reference to non-updatable view in the FROM clause.
  • Reference only to literal values.
  • Multiple references to any column of the base table.
  • If you create a view with the TEMPTABLE algorithm, you cannot update the view.
    其实，基本上也就是只有多表 join 才会是可更新view。
注意：99%的情况，我们不会去用可更新view。也不建议通过view去更新数据。
如果使用view更新数据，注意：WITH CHECK OPTION

查看数据库view是否可更新

select table_name, is_updatable 
from information_schema.views 
where table_schema = 'mysql_practice';

MySQL视图处理算法

MySQL view processing algorithms决绝了MySQL如何处理view，有三种算法：

  • MERGE
  • TEMPTABLE : 会建一个临时表，并把结果放进来。
  • UNDEFINED

默认是 UNDEFINED。

参考资料

  1. MySQL View Processing Algorithms
  2. Creating MySQL Updatable Views
    原文：MySQL 视图（View）

推荐阅读