MySQL数据库视图的创建与执行

2023-10-29 理论教育版权反馈

【摘要】：创建视图是通过CREATE VIEW语句实现的。当视图指定为TEMPTABLE时，将在创建视图的同时创建相应的TEMPORARY表。如果为视图指定了UNDEFINED算法，当查询指示其结果和视图结果是一对一关系时，MySQL将选择TEMPTABLE。DEFINER子句确定在视图执行时将检查哪个用户账户的权限来确定是否有适当的权限执行这个视图。

创建视图是通过CREATE VIEW语句实现的。其形式如下：

本节将介绍前述所有CREATE VIEW语法，不过现在先从一个简单的示例开始。假设corporate数据库包含一个表employee，它在逻辑上包含每位员工的信息。此表的创建语法如下：

开发人员需要执行以下任务：创建一个内部网界面，允许员工快速查看其同事联系方式信息。但是，因为工资是敏感数据，所以要求数据库管理员创建一个只由每位员工的姓名、电子邮件地址和电话号码组成的视图。如下视图提供了该信息的接口，按员工的姓氏对结果排序：

然后可以如下调用此视图：

这得到类似于下面的结果：

注意，在很多情况下，MySQL将视图看作一个表。事实上，在使用创建了视图的数据库时，如果执行SHOW TABLES（或者使用phpMyadmin或其他客户端执行某个类似任务），将看到视图会与其他表列在一起：

得到如下结果：

现在，对视图执行DESCRIBE语句：

得到如下结果：

可能会感到奇怪地了解到甚至可以创建可更新（updatable）视图。即可以向现有的视图插入记录，还可以更新视图中现有的行。

1.定制视图结果

记住，视图并不限于返回用于创建视图的查询中定义的每条记录。例如，可以只返回员工的姓氏和电子邮件地址：

这会返回类似于下面的结果：

还可以在调用视图时覆盖任何默认的排序子句。例如，employee_contact info view视图定义指定信息应当根据姓氏排序。但是，如果希望根据电话号码排序结果怎么办？只要修改子句，如下：

这会得到类似于下面的输出：

因此，视图可以与所有子句和函数联合使用，这意味着可以使用SUM（）、LOWER（）、ORDER BY、GROUPBY或任何其他能想象到的子句或函数。

2.传递参数

与使用子句和函数操作视图结果一样，也可以通过传递参数来操作视图结果。例如，假设希望获取某个特定员工的联系方式，只需记住他的名字：

得到结果如下：

3.修改返回的列名

表的列命名约定通常是为了方便程序员使用，而这有时候会让终端用户很难阅读。使用视图时，可以通过可选参数column list传入列名，让这些名字更可读。以下示例重新创建了employee_contact一info view视图，使用更友好的名字替换了默认的列名：

现在，执行如下查询：

得到结果如下：

4.使用ALGORITHM属性

ALGORITHM={MERCE | TEMPTABLE | UNDEFINED)(www.chuimin.cn)

通过使用MySQL特有的这个属性，可以优化MySQL视图的执行。有3个可用的设置，本节将介绍这3个设置。

➢ MERGE

MERGE算法使MySQL将执行视图时传入的任何子句合并到视图的查询定义中。例如，假设视图employee_contact_info_view使用如下查询定义：

但是，使用如下语句执行此视图：

MERGE算法实际上将导致执行如下语句：

换句话说，视图定义和SELECT查询会合并。

➢ TEMPTABLE

如果视图底层表中的数据有变化，这些变化将在下次通过视图访问表时立即反映出来。但是，当操作特别大或经待更新的表时，可能要首先考虑将视图数据转储到一个TEMPORARY表中，从而更快地释放视图对表的锁。

当视图指定为TEMPTABLE时，将在创建视图的同时创建相应的TEMPORARY表。

➢ UNDEFINED

当视图指定为UNDEFINED时，MySQL会确定应当使用两种算法（MERGE或TEMPTABLE）中的哪一种。虽然有些特定的情况下会首选TEMPTABLE（例如在查询中使用了聚集函数时），但一般情况下MERGE算法效率会更高。因此，除非从查询条件可以看出某种算法要优于另外一种，否则应当使用UNDEFINED（默认设置）。

如果为视图指定了UNDEFINED算法，当查询指示其结果和视图结果是一对一关系时，MySQL将选择TEMPTABLE。

5.使用安全选项

[DEFINER = {user | CURRENT_USER)]

[SQL SECURITY {DEFINER | INVOKER}]

MySQL 5.1.2为CREATE VIEW命令增加了一些额外的安全特性，它们有助于控制在每次执行视图时如何确定权限。

DEFINER子句确定在视图执行时将检查哪个用户账户的权限来确定是否有适当的权限执行这个视图。如果设置为默认值CURRENT_USER，就会检查执行视图的用户的权限；否则DEFINER子句可以设置为一个特定用户，该用户使用‘user@host’语法标识（例如，’jason@localhost‘）。只有拥有SUPER权限的用户才能够为另一个用户指定DEFINER。

SQL SECURITY子句确定执行视图时是否要检查视图创建者（DEFINER，然后它会查看前面提到的DEFINER子句的设置）或调用者（INVOKER）的权限。

6.WITH CHECK OPTION子句

因为可以根据其他视图创建视图，所以必须有一种方法确保更新内嵌的视图不会违反其定义的约束。此外，虽然有些视图是可更新的，但有些情况下以这种方法修改字段值是不合逻辑的，它会破坏视图底层查询的某些约束。例如，如果查询只获取city=”Columbus”的记录，然后创建一个包括WITH CHECK OPTION子句的视图，这会防止后面的视图更新将此字段的值改为Columbus之外的任何值。

最好通过一个例子来说明以这种方式修改MySQL行为的有关概念和选项。假设一个视图experienced_age_view定义有LOCAL CHECK OPTION选项，并包含如下查询：

注意，此查询引用了另一个视图experienced_view。假设该视图定义如下：

如果experienced_age_view定义有CASCADED CHECK OPTION选项，则尝试执行如下INSERT查询会失败：

失败的原因是years_experience的值3违反了experienced_age_view要求years_experlence至少为5年的约束。相反，如果experlenced_age_view视图定义为LOCAL，则INSERT查询就有效，因为只要求age大于65。但是，如果age设为小于65的值，例如42，则此查询将失败，因为LOCAL会检查查询中引用的视图，在这里是experienced_age_view。

MySQL数据库视图的创建与执行

相关推荐