深入浅出MySQL -- 视图

Posted on 2019-03-05 In Database , MySQL , 深入浅出MySQL Views: Symbols count in article: 2.9k Reading time ≈ 11 mins.

深入浅出MySQL -- 视图

视图

视图是虚拟存在的表, 对客户来说是透明的.

简单, 很多复杂条件已经提前组合好了
安全, 只能访问他们被允许的结果集
数据独立, 源表的变更对视图几乎无影响, 即使修改视图涉及的列名, 视图可以修改以适配.

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


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

目前不支持视图含有subquery
WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条

件。这个选项与 Oracle 数据库中的选项是类似的，其中：
LOCAL 是只要满足本视图的条件就可以更新；
CASCADED 则是必须满足所有针对该视图的所有视图的条件才可以更新。
如果没有明确是 LOCAL 还是 CASCADED，则默认是 CASCADED。

mysql> create or replace view payment_view as
 -> select payment_id,amount from payment 
 -> where amount < 10 WITH CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> 
mysql> create or replace view payment_view1 as
 -> select payment_id,amount from payment_view 
 -> where amount > 5 WITH LOCAL CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> 
mysql> create or replace view payment_view2 as
 -> select payment_id,amount from payment_view
  -> where amount > 5 WITH CASCADED CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from payment_view1 limit 1;
+------------+--------+ 
 ?| payment_id | amount |
+------------+--------+| 3 | 5.99 |
+------------+--------+1 row in set (0.00 sec)
mysql> update payment_view1 set amount=10 
  -> where payment_id = 3; 
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update payment_view2 set amount=10
  -> where payment_id = 3;
ERROR 1369 (HY000): CHECK OPTION failed 'sakila.paymentview2'

可选的ALGORITHM子句是对标准SQL的MySQL扩展。ALGORITHM可取三个值：MERGE、TEMPTABLE或UNDEFINED。如果没有ALGORITHM子句，默认算法是UNDEFINED（未定义的）。算法会影响MySQL处理视图的方式。

一、 MERGE算法
文档解释：

对于MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分

通俗的说，意思就是MERGE算法是一个合并算法，每当执行的时候,先将视图的sql语句与外部查询视图的sql语句,合并在一起,最终执行；这样操作对效率基本上没有什么影响，但是使用这种算法有一定限制，以下引自文档：

MERGE算法要求视图中的行和基表中的行具有一对一的关系。如果不具有该关系。必须使用临时表取而代之。如果视图包含下述结构中的任何一种，将失去一对一的关系：
·         聚合函数（SUM(), MIN(), MAX(), COUNT()等）。
·         DISTINCT
·         GROUP BY
·         HAVING
·         UNION或UNION ALL
·         仅引用文字值（在该情况下，没有基本表）。
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14

举个例子

/** 定义两张表
seller_sku表包括以下字段：id(主键),seller_id(门店ID),sku_id(商品sku的id,对应goods_sku的id),amount(商品的库存)
goods_sku 表包括以下字段：id(主键),goods_name(sku的名称)
**/
#案例1 查询商品库存大于50的门店的商品所对应的成本
CREATE OR REPLACE  VIEW amount_50_sku AS
SELECT
seller_id,
price,
sku_id,
amount,
(price*amount) AS sku_values 
FROM 
sellers_sku WHERE amount > 50
#方法A
SELECT * FROM amount_50_sku  
#方法B
SELECT seller_id,price,sku_id,amount,(price*amount) AS sku_values FROM sellers_sku 
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23

方法A和方法B查询出结果的时间是差不多的，MERGE算法对效率的影响很小。
二、TEMPTABLE算法
文档解释

对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。

TEMPTABLE算法是将结果放置到临时表中，意味这要mysql要先创建好一个临时表，然后将结果放到临时表中去，然后再使用这个临时表进行相应的查询。为什么文档中说“果使用了临时表，视图是不可更新的。”就是因为这个算法生成的视图其实就是一个结果的临时表，是无法执行update语句的，mysql会报错：

1 2	“错误代码： 1288 The target table seller_sku_amount of the UPDATE is not updatable" • 1

最最重要的是，TEMPTABLE算法会创建临时表，这个过程是会影响效率的，如以下案例：

/**
seller_sku表包括以下字段：id(主键),seller_id(门店ID),sku_id(商品sku的id,对应goods_sku的id),amount(商品的库存)
goods_sku 表包括以下字段：id(主键),goods_name(sku的名称)
**/
#案例2 显示每个商品各个门店库存的总和
#创建视图
CREATE OR REPLACE VIEW seller_sku_amount AS SELECT
sku_id,
SUM(amount) AS amount_total
FROM sellers_sku
GROUP BY sku_id
#使用视图查询
SELECT 
seller_sku_amount.sku_id,
seller_sku_amount.amount_total,
goods_sku.*
 FROM seller_sku_amount JOIN goods_sku ON goods_sku.`id` = seller_sku_amount.`sku_id`
 
 
#原生SQL查询
SELECT
sellers_sku.sku_id,
SUM(sellers_sku.amount) AS amount_total,
goods_sku.*
FROM sellers_sku
JOIN goods_sku ON sellers_sku.`sku_id` = goods_sku.`id`
GROUP BY sku_id
ORDER BY amount_total DESC
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32

以上两个查询，使用视图查询的速度为比使用原生SQL查询的效率慢50%，随着数据量的增大，这个效率还会更慢（数据量越大，需要往临时表填充更多的数据）；
但是TEMPTABLE算法也不是没有好处的，TEMPTABLE算法创建临时表之后、并在完成语句处理之前，能够释放基表上的锁定。与MERGE算法相比，锁定释放的速度更快，这样，使用视图的其他客户端不会被屏蔽过长时间。
三、UNDEFINED算法
UNDEFINED算法没啥好区分的，直接引用文档的：

对于UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

视图