0%

视图

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

  1. 简单, 很多复杂条件已经提前组合好了
  2. 安全, 只能访问他们被允许的结果集
  3. 数据独立, 源表的变更对视图几乎无影响, 即使修改视图涉及的列名, 视图可以修改以适配.
1
2
3
4
5
6
7
8
9
10
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]
  1. 目前不支持视图含有subquery
  2. WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条

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

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
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'


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

一、 MERGE算法
文档解释: > 对于MERGE,会将引用视图的语句的文本与视图定义合并起来,使得视图定义的某一部分取代语句的对应部分

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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
举个例子
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
33
34
35
36
37
38
39
40
41
/** 定义两张表
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算法会创建临时表,这个过程是会影响效率的,如以下案例:
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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
/**
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通常更有效,而且如果使用了临时表,视图是不可更新的。

视图

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

  1. 简单, 很多复杂条件已经提前组合好了
  2. 安全, 只能访问他们被允许的结果集
  3. 数据独立, 源表的变更对视图几乎无影响, 即使修改视图涉及的列名, 视图可以修改以适配.
1
2
3
4
5
6
7
8
9
10
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]
  1. 目前不支持视图含有subquery
  2. WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条

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

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
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'


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

一、 MERGE算法
文档解释: > 对于MERGE,会将引用视图的语句的文本与视图定义合并起来,使得视图定义的某一部分取代语句的对应部分

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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
举个例子
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
33
34
35
36
37
38
39
40
41
/** 定义两张表
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算法会创建临时表,这个过程是会影响效率的,如以下案例:
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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
/**
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通常更有效,而且如果使用了临时表,视图是不可更新的。