MySQL视图之我见

最近项目上有使用到数据库视图，觉得需要把相关的知识整理一下方便学习。今天先简单介绍一下视图的概念和一些基本原理。

什么是数据库视图

数据库视图的创建是基于SQL SELECT query和JOIN的。视图和表很相似，它也包含行和列，所以可以直接对它进行查询操作。另外大多数的数据库同样允许进行UPADTE操作，但必须满足一定的条件。视图的数据结构如图：

image

我们需要理解，数据库并没有存储视图所关联的数据，存储的只是视图的定义也就是相应的SQL SELECT and JOIN。

那么使用数据库视图到底有哪些优势呢：

• 视图可以简化你的复杂查询：视图的定义是基于一个查询声明，这个查询声明可能关联了很多底层表。我们可以使用视图向数据库的使用者或者外部程序隐藏复杂的底层表关系。
• 视图可以限制特定用户的数据访问权：有时我们希望隐藏某些表的一些数据对一些特定用户，这时视图可以很好的帮助我们实现这个功能。
• 视图可以使用可计算的列：我们知道表的列一般都不支持动态计算，但是视图的列是支持的。假设在有一张order_details表，其中包含product_nums和price_each两列，当我们需要查询order总价时我们就需要查询出结果后在代码中进行计算，如果我们使用视图的话可以在视图中添加一列total_price(product_nums*price_each)。这样就可以直接查询出order的总价。
• 视图可以帮助我们兼容旧的系统：假设我们拥有一个数据中心，这个数据中心被很多的程序在使用。如果有一天我们决定重新设计这个数据中心以适应一些新的业务需求，可能需要删除一些旧的表，并且创建一些新的表，但是我们并不希望这些变动影响到那些老的程序。那么我们可以创建一些视图用来适配那些老的程序。

MySQL View

MySQL从5.x版本支持视图，并且基本符合SQL: 2003标准。
MySQL中执行查询视图的方式有一下两种：

• MySQL会合并输入的查询语句和视图的查询声明然后执行合并后的语句并返回结果。
• MySQL会基于视图的查询声明创建一个temporary table, 当执行查询语句时会查询这张temporary table

如果创建视图的时候并未指定查询方式，MySQL会默认优先使用第一种，但如果视图的查询声明中的SELECT使用了聚合函数(MIN, MAX, SUM, COUNT, AVG, etc., or DISTINCT, GROUP BY, HAVING, LIMIT, UNION, UNION ALL, subquery.)，那么视图查询会使用第二种方式。

Create View

创建MySQL视图可以使用CREATE VIEW声明：

CREATE 
   [ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]
VIEW [database_name].[view_name] 
AS
[SELECT  statement]

ALGORITHM：

MySQL有三种视图执行策略, 分别是MERGE, TEMPTABLE, UNDEFINED.

• 使用MERGE策略，MySQL会先将输入的查询语句和视图的声明语句进行合并，然后执行合并后的语句并返回。但是如果输入的查询语句中不允许包含一些聚合函数如: MIN, MAX, SUM, COUNT, AVG, etc., or DISTINCT, GROUP BY, HAVING, LIMIT, UNION, UNION ALL, subquery。同样如果视图声明没有指向任何数据表，也是不允许的。如果出现以上任意情况, MySQL默认会使用UNDEFINED策略。
• 使用TEMPTABLE策略，MySQL先基于视图的声明创建一张temporary table，当输入查询语句时会直接查询这张temporary table。由于需要创建temporary table来存储视图的结果集, TEMPTABLE的效率要比MERGE策略低，另外使用temporary table策略的视图是无法更新的。
• 使用UNDEFINED策略，如果创建视图的时候不指定策略，MySQL默认使用此策略。UNDEFINED策略会自动选择使用上述两种策略中的一个，优先选择MERGE策略，无法使用则转为TEMPTABLE策略。

View Name

视图的名称，在MySQL中视图名和表名使用同一命名空间，这意味这视图名不能和表名重复并且要符合表名的命名规范。

Select Statement

在视图的查询声明中你可以查询所有数据库中已存在的表的数据，有以下集中规则：

• 查询声明可以在WHERE条件中使用子查询但是不允许在FROM的来源中使用子查询。
• 查询声明中不允许引用任何变量，包括local variables, user variables, and session variables。
• 查询声明中不允许引用prepare statement的参数。

Note: 查询声明的From中可以引用其他视图

Example

• 使用INNNER JOIN

CREATE VIEW customerOrders AS
    SELECT 
        d.orderNumber,
        customerName,
        SUM(quantityOrdered * priceEach) total
    FROM
        orderDetails d
            INNER JOIN
        orders o ON o.orderNumber = d.orderNumber
            INNER JOIN
        customers c ON c.customerNumber = c.customerNumber
    GROUP BY d.orderNumber
    ORDER BY total DESC;

• 使用子查询

CREATE VIEW aboveAvgProducts AS
    SELECT 
        productCode, productName, buyPrice
    FROM
        products
    WHERE
        buyPrice > 
 (SELECT 
                AVG(buyPrice)
            FROM
                products)
    ORDER BY buyPrice DESC;

查询视图

SELECT * FROM customerOrders;